CODEWITHSUNDEEP
unixstring-concatenation
Saurabh
Saurabh

Reputation: 15

Unix Concat multiple lines between specific keywords into a single line

My team gets Teradata DDL files generated through a front end tool. These files need to be corrected before executing. A step in this is getting the DDL command on a single line

E.g.
create table ABC
(column A varchar2(100),
column B number(10)
);

replace view ABC_v as
select columnA, column B from
ABC;

should change to

create table ABC (column A varchar2(100),column B number(10));

replace view ABC_v as select columnA, column B from ABC;

In short, I am looking to replace every new line character with single space in a multi-line string.

The string can start with either create, replace or drop and it will always end with a ; (semicolon)
Thanks in advance for your help

Upvotes: 0

Views: 183

Answers (1)

ghoti
ghoti

Reputation: 46856

Here's a simple solution in shell:

#!/bin/sh

while read first rest; do
  case "$first" in
    create|replace|drop) echo "" ;;
  esac
  printf "%s %s " "$first" "$rest"
done < inputfile
echo ""

This adds a blank line to the beginning of the output because I'm lazy. But you see the logic, I'm sure. To avoid the blank line, you can use a temporary variable to determine whether you've actually started pulling in input data yet.

You could do something sort-of similar using awk:

awk '
  BEGIN {
    a["create"];
    a["replace"];
    a["drop"];
  }

  $1 in a && h {
    print substr(h,2);h="";
  }

  {
    h=h" "$0;
  }

  END {
    print substr(h,2);
  }
' inputfile

Instead of simply prepending a newline before keywords, this solution builds lines of output in variables, then prints them when they're complete.

Alternately, you could use sed to implement the same idea:

sed -rne '/^(create|replace|drop) /{;x;s/\n/ /g;/./p;d;};H;${;x;s/\n/ /g;p;}' inputfile

In all three of these solutions, I haven't bothered to check whether the input string ends in a semicolon. You can add that check to each of them once you decide how you want to handle that failure. (Report an error? Send the command via email? Ignore it?)

Note also that DDL, like SQL, should be able to interpret commands provided on multiple lines. SQL is whitespace agnostic -- an unquoted newline should be the same as a space (though perhaps Teradata behaves differently).

Upvotes: 1

Related Questions