File formatting

Shell Programming and Scripting
1

Hi,

I have a file which contains data in this format

# User@Host: abc @  [3.210.193.65]  Id: 0000000
# Query_time: 0.000070  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=00000000;
SELECT @@version, @@version_comment;
# User@Host: abcd @  [3.210.193.65]  Id: 00000000 
# Query_time: 0.000228  Lock_time: 0.000070 Rows_sent: 40  Rows_examined: 40
SET timestamp=00000000;
SELECT * FROM information_schema.CHARACTER_SETS;
# User@Host: abcde @  [3.210.193.65]  Id: 000000000
# Query_time: 0.000002  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=00000000;
SET NAMES 'utf8';
# User@Host: abcdef @  [3.210.193.76]  Id: 0000000
# Query_time: 0.000022  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=00000000;
SET NAMES 'utf8';

Want to take values which contains select queries only.

Output file should be like this

 
# User@Host: abc @  [3.210.193.65]  Id: 0000000
SELECT @@version, @@version_comment;

# User@Host: abcd @  [3.210.193.65]  Id: 00000000
SELECT * FROM information_schema.CHARACTER_SETS;

Please suggest.:wall:

---------- Post updated at 09:37 PM ---------- Previous update was at 09:36 PM ----------

the input file contains many other entries like this.Pasted only a part of that file.

2 
awk '/SELECT/ { print ""; print UH; print; UH=""; next}; /#/ && /@/ { UH=$0 }' datafile

# User@Host: abc @  [3.210.193.65]  Id: 0000000
SELECT @@version, @@version_comment;

# User@Host: abcd @  [3.210.193.65]  Id: 00000000
SELECT * FROM information_schema.CHARACTER_SETS;

$
3

Hi,

actually my input files contains some insert,update and delete queries also. I want them also in the same format as select.

But when i add insert,update and delete in that i am getting error.

please suggest.

4

What exactly did you do, and what error did you get? Show details.

5

hi,

I am getting this error when trying to find the insert,delete and update qureries along with the select queries.

sudo awk '/SELECT/INSERT/UPDATE/DELETE { print ""; print UH; print; UH=""; next}; /#/ && /@/ { UH=$0 }' test1_file_new1.txt >> test77.txt

awk: (FILENAME=test1_file_new1.txt FNR=1) fatal: division by zero attempted

Please suggest.
:confused:

6 
sudo awk '/SELECT/INSERT/UPDATE/DELETE

change to with and re-try

/(SELECT|INSERT|UPDATE|DELETE)/
7

Modified the script in this way and it worked.

awk '/SELECT/||/UPDATE/||/INSER/||/DELETE/ { print ""; print UH; print; UH=""; next}; /#/ && /@/ { UH=$0 }' datafile