How to extract queries using UNIX shell script?

Shell Programming and Scripting
1

Hi,

I have an input file which have many lines,from which i need to extract only the complete sql statements and write this alone to an output file.

please help us in this.

Regards
Meva

2

Post an example of your input file and the desired output between code tags.

3

the sample input

The Expected output should be in a seperate output file with the sql statements alone.

Any suggestions?

4

You want the insert statement to begin 'INSERT and the update statement to begin -update, I assume this is a typo?I have never seen SQL that begins and ends all statements with a single quote, are you sure this is the output you require? SQL statements that contain textual data generally delimit it with single quotes within the statement. Will you require this as well as containing the whole statement within single quotes?

5

Works only according to the data you posted :

awk '$0 ~ /INSERT/ {flag=1;} /UPDATE/ {flag=1;} $0 ~ /)/ { flag=0 ;va=va"\n"$0;next} {if(flag==1) {va=va"\n"$0} } END {print va }' input_file.txt
6

Expected output

---------- Post updated at 05:47 AM ---------- Previous update was at 05:34 AM ----------

Hi Panyam

I tried the code you have posted, but i do get other lines as well other than the SQL statements.

the input file will have the SQL statements as well as some other lines. We need to extract only the SQL.

Thanks in Advance.
Meva

---------- Post updated at 06:02 AM ---------- Previous update was at 05:47 AM ----------

Hi Panyam

If you could explain me the command you have given me for this, i could also try to modify it further to get the desired result.

Thanks
Meva

7

Try this :

awk 'BEGIN{va=" "} $0 ~ /INSERT/ {flag=1;va=va"\n\n INSERT";next} $0 ~ /UPDATE/ {flag=1;va=va"\n\n UPDATE";next} $0 ~ /)'"'"'/   { flag=0 ;va=va"\n"substr($0,1,index($0,")"));next} {if(flag==1) {va=va"\n"$0} } END {print va }' file_name.txt

try to put the script in a file and execute it.

8

gosh,
don't squeeze the whole code into one long line like this. its hard to read and troubleshoot. indent the code where necessary.