# Shell script: substitute value in text file

**URL:** <https://community.unix.com/t/shell-script-substitute-value-in-text-file/270182>\
**Category:** Shell Programming and Scripting\
**Created:** [July 24, 2010, 9:39am UTC](https://community.unix.com/t/shell-script-substitute-value-in-text-file/270182 "2010-07-24T09:39:37Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![mirfan](https://community.unix.com/letter_avatar/mirfan/32/6_22734026db12803ebb3e059622ac3534.png) [@mirfan](https://community.unix.com/u/mirfan)\
**Post date:** [July 24, 2010, 9:39am UTC](https://community.unix.com/t/shell-script-substitute-value-in-text-file/270182/1 "2010-07-24T09:39:37Z")

</div>

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

```nohighlight
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.

```nohighlight
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.

---

<div class="post-metadata">

**Author:** ![Franklin52](https://community.unix.com/user_avatar/community.unix.com/franklin52/32/500_2.png) [@Franklin52](https://community.unix.com/u/Franklin52)\
**Post date:** [July 24, 2010, 10:10am UTC](https://community.unix.com/t/shell-script-substitute-value-in-text-file/270182/2 "2010-07-24T10:10:16Z")

</div>

Try something like this:

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

```

---

<div class="post-metadata">

**Author:** ![KenJackson](https://community.unix.com/letter_avatar/kenjackson/32/6_22734026db12803ebb3e059622ac3534.png) [@KenJackson](https://community.unix.com/u/KenJackson)\
**Post date:** [July 25, 2010, 9:52am UTC](https://community.unix.com/t/shell-script-substitute-value-in-text-file/270182/3 "2010-07-25T09:52:23Z")

</div>

You could also use sed:

```nohighlight
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 );.

---

<div class="post-metadata">

**Author:** ![Franklin52](https://community.unix.com/user_avatar/community.unix.com/franklin52/32/500_2.png) [@Franklin52](https://community.unix.com/u/Franklin52)\
**Post date:** [July 25, 2010, 10:45am UTC](https://community.unix.com/t/shell-script-substitute-value-in-text-file/270182/4 "2010-07-25T10:45:10Z")

</div>

> [@kenjackson](#):
>
> You could also use sed:
> 
> ```plaintext
> 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 );.

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.

---

<div class="post-metadata">

**Author:** ![rdcwayx](https://community.unix.com/letter_avatar/rdcwayx/32/6_22734026db12803ebb3e059622ac3534.png) [@rdcwayx](https://community.unix.com/u/rdcwayx)\
**Post date:** [July 25, 2010, 8:32pm UTC](https://community.unix.com/t/shell-script-substitute-value-in-text-file/270182/5 "2010-07-25T20:32:10Z")

</div>

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

```

---

<div class="post-metadata">

**Author:** ![mirfan](https://community.unix.com/letter_avatar/mirfan/32/6_22734026db12803ebb3e059622ac3534.png) [@mirfan](https://community.unix.com/u/mirfan)\
**Post date:** [July 26, 2010, 11:57am UTC](https://community.unix.com/t/shell-script-substitute-value-in-text-file/270182/6 "2010-07-26T11:57:35Z")

</div>

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:

```nohighlight
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

```nohighlight
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.

```nohighlight
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

```nohighlight
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.

---

<div class="post-metadata">

**Author:** ![Franklin52](https://community.unix.com/user_avatar/community.unix.com/franklin52/32/500_2.png) [@Franklin52](https://community.unix.com/u/Franklin52)\
**Post date:** [July 26, 2010, 1:11pm UTC](https://community.unix.com/t/shell-script-substitute-value-in-text-file/270182/7 "2010-07-26T13:11:04Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![KenJackson](https://community.unix.com/letter_avatar/kenjackson/32/6_22734026db12803ebb3e059622ac3534.png) [@KenJackson](https://community.unix.com/u/KenJackson)\
**Post date:** [July 26, 2010, 9:02pm UTC](https://community.unix.com/t/shell-script-substitute-value-in-text-file/270182/8 "2010-07-26T21:02:58Z")

</div>

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

I think this will work.

```nohighlight
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.

---

<div class="post-metadata">

**Author:** ![Franklin52](https://community.unix.com/user_avatar/community.unix.com/franklin52/32/500_2.png) [@Franklin52](https://community.unix.com/u/Franklin52)\
**Post date:** [July 27, 2010, 2:10am UTC](https://community.unix.com/t/shell-script-substitute-value-in-text-file/270182/9 "2010-07-27T02:10:43Z")

</div>

> [@kenjackson](#):
>
> LENGTH? REPLACE? in sed? That looks like awk.

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

```plaintext
LENGTH(REPLACE(c, ' ', ''))

```

with:

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

```