Replace pattern matching

Shell Programming and Scripting
1

Can anyone help me with sed or awk to do a bulk replace of the below requirements.

"REC_ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (
                    START WITH +7486
                    INCREMENT BY +1
                    MINVALUE +7467
                    MAXVALUE +2147483647
                    NO CYCLE
                    CACHE 20
                    NO ORDER ) )

The above needs to be replaced with

"REC_ID" INTEGER NOT NULL

##############################################

REC_ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (
                    START WITH +241
                    INCREMENT BY +1
                    MINVALUE +222
                    MAXVALUE +2147483647
                    NO CYCLE
                    CACHE 20
                    NO ORDER ) ,
                  "SYS_START" TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW BEGIN ,
                  "SYS_END" TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW END ,
                  "TRANS_START" TIMESTAMP(12) IMPLICITLY HIDDEN GENERATED ALWAYS AS TRANSACTION START ID ,
                 PERIOD SYSTEM_TIME ("SYS_START","SYS_END") )

The above needs to replaced to

"REC_ID" INTEGER NOT NULL,
        "SYS_START" TIMESTAMP(12) NOT NULL ,
        "SYS_END" TIMESTAMP(12) NOT NULL ,
        "TRANS_START" TIMESTAMP(12) )
2

Any attempts / ideas / thoughts from your side?
Why is PERIOD SYSTEM_TIME ("SYS_START","SYS_END") not found in the desired output?

3

RudiC - My apologies for not using the code. Thanks for making it code format. The statement that you mentioned is not required for the output. It is not considered as a column in the target database. So it is not required. Appreciate for any support.

4

I'm afraid its not too clear how to identify the partial lines / statements to be removed. While it were pretty easy to remove anything after e.g. "NOT NULL" or starting with "IMPLICITLY" or "GENERATED" I'm pretty sure someone would come back with yet another statement for which that approach would not work. On top, there seems to be an opening parethesis missing.

Please specify exactly how to identify the to be removed parts.

5

I'm fine with after NULL till the NO ORDER.

You see a paranthesis because its part of CREATE TABLE command,

CREATE TABLE (
           Column1 int,
            Column2 int,
             Rec_id int )

This is the reason you see a paranthesis at the last.

6

Try this as a starting point

awk '
        {while (!gsub (/[),] *$/, "&")) {getline X
                                         $0 = $0 " " X
                                        }
         sub (/ *$/, _)
         EOL = substr ($0, length)
         sub (/(GENERATED|IMPLICITLY).*$/, _)
         $0 = $0 EOL
        }
1
' file
"REC_ID" INTEGER NOT NULL )
"REC_ID" INTEGER NOT NULL ,
                  "SYS_START" TIMESTAMP(12) NOT NULL ,
                  "SYS_END" TIMESTAMP(12) NOT NULL ,
                  "TRANS_START" TIMESTAMP(12) ,
                 PERIOD SYSTEM_TIME ("SYS_START","SYS_END") ))
7

Another one that ony looks for corresponding brackets.

awk '
match($0,/"REC_ID" INTEGER NOT NULL/) {
  print substr($0,RSTART,RLENGTH)
  cut=1; blevel=0 
}
cut==1 {
  x=$0; blevel=blevel+gsub(/[(]/,"",x)-gsub(/[)]/,"",x)
}
blevel<=0 {
  if (cut==1) { sub(/^[^)]*[)]/,""); cut=0 }
  print
}
' file