Join or merge lines between two patterns inclusive (sed or awk)

Question

I have a large file with multi line SQL statements that I need to "join" into one liners. Consider the following:

create unique index <bla1 bla1>
    <more bla1 bla1>;
alter table <bla2 bla2>
    <more bla2 bla2>;
create index <bla3 bla3>
    <more bla3 bla3>;
alter table <bla4 bla4>
    <more bla4 bla4>
    <some more bla4 bla4>;

in which pattern1 would be "alter table" and pattern2 ";". I would like the output to be:

create unique index <bla1 bla1>
    <more bla1 bla1>;
alter table <bla2 bla2> <more bla2 bla2>;
create index <bla3 bla3>
    <more bla3 bla3>;
alter table <bla4 bla4> <more bla4 bla4> <some more bla4 bla4>;

I've seen other posts about merging lines with sed or awk (closest being sed join lines together, and How to print lines between two patterns, inclusive or exclusive (in sed, AWK or Perl)? kinda gave me a couple of ideas), but they can't quite accomplish the task. While I prefer the answer in sed or awk, any others will be appreciated. Thanks in advance!

How does that first linked approach fail? And are those leading tabs or spaces? — Beta, May 13 '20 at 18:14
How does that first linked approach fail? - it joins when the last field is "0". I can extrapolate to say last character is ";" but that would join the "create index" too, which I don't want. And are those leading tabs or spaces? - They are spaces. — FanDeLaU, May 13 '20 at 18:34
I don't have a good version of sed handy (long story), but try this: `sed ':a;/^alter table/{/;$/!{N;s/\n //;ba}}'` — Beta, May 13 '20 at 19:17
@Beta - It works great! I need to handle some unsightly extra spaces, but it works as desired! Add your suggestion as an answer so I can select it as the accepted answer.Also, a quick explanation would be appreciated... thanks!!! — FanDeLaU, May 13 '20 at 19:48

Beta · Accepted Answer · 2020-05-13T20:29:32.527

1

Try this:

sed ':a;/^alter table.*[^;].$/{N;s/\n //;ba}'

EDIT: That's what comes of not having the real tool to experiment with. All right, back to this one:

sed ':a;/^alter table/{/;$/!{N;s/\n //;ba}}'

edited May 13 '20 at 20:29

answered May 13 '20 at 19:59

Beta

96,650
16
149
150

actually the response you posted in the comments above worked great. this one, not so much... ^_^ – FanDeLaU May 13 '20 at 20:05

Join or merge lines between two patterns inclusive (sed or awk)

1 Answers1