Sed replace a group of line files

Question

I am trying to replace a string in multiple lines. It's getting late, and I'm getting restless, maybe someone would rather give him a chance at some DOTS. The line I'm replacing is "STORED AS TEXTFILE" from SQL below ...

PARTITIONED BY(load string, date string, source_file string)
STORED AS TEXTFILE
LOCATION '${staging_directory}/${tablename}';

And so that he looks like ...

PARTITIONED BY(load string, date string, source_file string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '${staging_directory}/${tablename}';

So my expression

:%s/)\nSTORED AS TEXTFILE\nLOCATION '/)\rROW FORMAT DELIMITED \rFIELDS TERMINATED BY ',' \rSTORED AS TEXTFILE \rLOCATION '/g

What works inside the file (using vim), but I can not get it to work with one command in all files in the directory. I have tried so far ...

sed -i "s/)\nSTORED AS TEXTFILE\nLOCATION '/)\rROW FORMAT DELIMITED \rFIELDS TERMINATED BY ',' \rSTORED AS TEXTFILE \rLOCATION '/g"

... and I also tried the above statement with all spaces. Please, help!

Answer 1

gawk - GNU awk 4.1.0:

gawk -i inplace '$0~/STORED AS TEXTFILE/{ $0="ROW FORMAT DELIMITED" ORS "FIELDS TERMINATED BY \047,\047" ORS $0 }1' file*

• -i inplace -

Answer 2

sed , https://unix.stackexchange.com/questions/26284/how-can-i-use-sed-to-replace-a-multi-line-string

perl $/ undefined ( ):

perl -i.BAK -pe 'BEGIN{undef$/}s/.../.../g' file

GNU sed -z, , NUL ($\="\0"), undef $/ .