Shell script: substitute value in text file

Shell Programming and Scripting
1

Hi gurus,

I need help with shell script. I have various INSERT queries which inserts data into database. I want to insert 3rd column data into newline for one particular table. I have very time long txt file everytime and it have various INSERT/UPDATE queries but i have to done with it only one table. Here is input

INSERT INTO test1 column1,column2 VALUES (column1value,column2value);
INSERT INTO test2 column1,column2,column3 VALUES (column1value,column2value,column3value);
INSERT INTO test3 column1 VALUES (column1value);

I need following as output so when database recieves \n character it will inserts data in new line i believe. I need this with only test2 table. I am not sure how to match pattern because everytime test2 table column2value is unique. I thaught it can be done by sed or awk.

INSERT INTO test1 column1,column2 VALUES (column1value,column2value);
INSERT INTO test2 column1,column2,column3 VALUES (column1value,column2value,\ncolumn3value);
INSERT INTO test3 column1 VALUES (column1value);

Any help appreciated.

2

Try something like this:

awk -F, '/INSERT INTO test2/ {$((NF+5)/2)="\\n" $((NF+5)/2)}1' OFS="," file
3

You could also use sed:

sed -i '/INTO test2 / s/,\([^,]*);\)/,\\n\1/' file

Notice the space after 2, which guarantees you will not match 20.
\( ... \) captures text that is copied to the output using \1.
[^,]*); matches zero or more non-comma characters (column3value) followed by );.

4

This command don't add the "\n" in the 3th column if you have more then 3 columns, but may be sufficient for the purpose of the OP.

5 
awk  'BEGIN{FS=OFS=","}/INTO test2/{$NF="\\n"$NF}1' urfile
6

Hello,

Franklin, i tried your command

awk -F, '/INSERT INTO test2/ {$((NF+5)/2)="\\n" $((NF+5)/2)}1' OFS="," inputfile

It outputs perfectly as "\ncolumn3value" but i forgot to mention that column3value is in single quote it results "\ncolumn3value" while i want it to be as '\ncolumn3value' within single quotes.

Origninal i have to try it with UPDATE query and output should be as follows \n character need to be in single quotes:

UPDATE test2 set c = Concat(chat, 'testing '),s_count = '0', b_count = '0',t_lines = LENGTH(c) - LENGTH(REPLACE(c, '\n ', '')), c_end = now() where id = '504';
[\code]

Franklin, can you please also explain query.

Thank you for your help.

---------- Post updated at 07:54 AM ---------- Previous update was at 05:09 AM ----------

this is the input.


UPDATE test2 set c = Concat(chat, 'testing '),s_count = '0', b_count = '0',t_lines = LENGTH(c) - LENGTH(REPLACE(c, '\n ', '')), c_end = now() where id = '504';

and output should be

UPDATE test2 set c = Concat(chat, 'testing '),s_count = '0', b_count = '0',t_lines = LENGTH(c) - LENGTH(REPLACE(c, '\n ', '')), c_end = now() where id = '504';

---------- Post updated at 07:57 AM ---------- Previous update was at 07:54 AM ----------

Ah.. sorry for the mistake in iput file.
this is the input.

UPDATE test2 set c = Concat(chat, 'testing '),s_count = '0', b_count = '0',t_lines = LENGTH(c) - LENGTH(REPLACE(c, ' ', '')), c_end = now() where id = '504';

and output should be

UPDATE test2 set c = Concat(chat, 'testing '),s_count = '0', b_count = '0',t_lines = LENGTH(c) - LENGTH(REPLACE(c, '\n ', '')), c_end = now() where id = '504';

while no other command works for me mentioned by rdcwayx and KenJackson.

7

The solutions above is not suitable for arbitrary lines, for the line starting with "UPDATE" you could do it straight with sed like this:

sed "/UPDATE test2/ s/LENGTH(REPLACE(c, ' ', ''))/LENGTH(REPLACE(c, '\\\n ', ''))/" file
8

LENGTH? REPLACE? in sed? That looks like awk.

I think this will work.

sed -i "/INTO test2 / s/\(([^,]*,[^,]*,\)\([^,)]*\)/\1'\\n\2'/" file

\(([^,]*,[^,],\) captures a paren, (, two fields and the commas that follow each,
\([^,)]\) captures the third field, but stops before the delimiting comma or paren,
\1 prints what was captured in the first parens,
'\\n\2' prints the third field, with additions.

9

It has nothing to do with awk :), the sed command substitutes:

LENGTH(REPLACE(c, ' ', ''))

with:

LENGTH(REPLACE(c, '\\\n ', ''))