Replace all the words between starting and end pattern using sed command

Question

Lets say that I have a file with below content:

Create table table_new as (
Select colA1,colA2,colA3
from tableA,
(Select col1 from tableB)

Now I want to replace everything between Create and from (first occurrence) words with space. i.e I want the output

tableA,
(Select col1 from tableB)

I tried the below approach.

1. replaced the new line character with # (random choice), so that I can use sed command.

2. tried the command sed -r 's/Create[^from].*from//ig' filename > new_file_name

The above command for me is replacing Create to 2nd occurrence of from and I'm remaining with "tableB)"

Could any one please help me how I can replace only till the first from word? It would be really helpful if you could explain the answer as well.

Answer 1

Not sure if this is want you are asking.

tr '\n' '#' < file.txt break the whole file into a single line so that sed can do the job.

^Create.*\?from matches everything from the beginning till one whitespace after the first "from".

\(.*\?from\) captures "tableA, (Select col1 from".

\1 refers to the above capture group.

tr '\n' '#' < file.txt | sed 's/^Create.*from \(.*from\)/\1/g; s/#/\n/g'

So the first sed operation should act like:

sed 's/Create table table_new as ( Select colA1,colA2,colA3 from tableA, (Select col1 from/tableA, (Select col1 from/g'

I think this solution is not very elegant, hope someone could come up with a better idea :(