Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

sed string manipulation in shell script

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting sed string manipulation in shell script
# 1  
Old 05-14-2010
sed string manipulation in shell script
Hello,

I have 1000 of sql queries and i need to push column value in query. e.g.

Code:
SET INSERT_ID=1
INSERT INTO test (id,name) VALUES ('a');
SET INSERT_ID=2
INSERT INTO test (id,name) VALUES ('b');
SET INSERT_ID=3
INSERT INTO test (id,name) VALUES ('c');
SET INSERT_ID=4
INSERT INTO test (id,name) VALUES ('d');

I need to put id column value for those sql queries. Resultant queries should be

Code:
INSERT INTO test (id,name) VALUES (1,'a');
INSERT INTO test (id,name) VALUES (2,'b');
INSERT INTO test (id,name) VALUES (3,'c');
INSERT INTO test (id,name) VALUES (4,'d');

I wrote shell script for that

Code:
# !/bin/bassh

ID=`cat test.sql | grep INSERT_ID | awk -F"=" '{print $2}' | sed 's/\(.*\)....../\1/'`
VALUES=`cat test.sql | grep "INSERT INTO test"

for ff in $VALUES
do

        for f in $ID
        do
        sed "s/VALUES (/VALUES ($f,/" $ff > finalfile.sql
        done
done

Where ID variable contains values 1,2,3,4... and so on
and VALUES variable contains SQL queries are

Code:
INSERT INTO test (id,name) VALUES ('a');
INSERT INTO test (id,name) VALUES ('b'); and so on

I applied nested loop and finally trying to push id column values. but it errors "filename too long". Following is the command

Code:
sed "s/VALUES (/VALUES ($f,/" "$ff" > finalfile.sql

command after value substituion
Code:
sed "s/VALUES (/VALUES (1,/" "INSERT INTO test (id,name) VALUES ('a');"

"Filename too long" error occus on %ff variable value i.e. on sql qurey.

Can someone please help how to accomplish it ?


Thank you.
Last edited by Franklin52; 05-14-2010 at 07:45 AM.. Reason: Please use code tags!
# 2  
Old 05-14-2010
Try:
Code:
awk -F"[ =]" '
/^SET/{id="(" $3 ","}
/^INSERT/{sub("\\(", id ,$NF);print}
' file

# 3  
Old 05-14-2010
Maybe this will work for you?
Code:
while read line; do
if echo "$line" | grep ^SET ; then
id=$(echo "$line" | cut -d= -f2)
else
echo "$line" | sed "s/VALUES (/VALUES ($id,/"
fi
done <input | sed '/^SET/d;'

# 4  
Old 05-14-2010
MySQL
Code:
 # cat dat
SET INSERT_ID=1
INSERT INTO test (id,name) VALUES ('a');
SET INSERT_ID=2
INSERT INTO test (id,name) VALUES ('b');
SET INSERT_ID=3
INSERT INTO test (id,name) VALUES ('c');
SET INSERT_ID=4
INSERT INTO test (id,name) VALUES ('d');

Code:
 
# ./sc
INSERT INTO test (id,name) VALUES ('1,a');
INSERT INTO test (id,name) VALUES ('2,b');
INSERT INTO test (id,name) VALUES ('3,c');
INSERT INTO test (id,name) VALUES ('4,d');

Code:
 # cat sc

#!/bin/bash
id=$( (sed -n '/=/p' dat | sed 's/SET INSERT_ID=//') )
char=$( (sed -e "s/.*VALUES ('\([a-z]\)');/\1/" -e "/SET INSERT/d" dat) )
idgr=( $id ) ; chargr=( $char )
idcount=`echo $id | tr -d [:space:]` ; count=${#idcount}
while [ $((count -= 1 )) -gt -1 ]
  do
    sed -e "s/('${chargr[i]}')/('${idgr[i]},${chargr[i]}')/" -e '/^SET INSERT_ID/d' dat | sed -n "/('${idgr[i]},${chargr[i]}')/p"
    ((++i))
  done

Last edited by ygemici; 05-14-2010 at 11:05 AM.. Reason: tag editing
# 5  
Old 05-15-2010
Hello,

None of the commands work for me. Anyways when i run following on commandline gives me error. Please let me know how to execute this

Code:
 
sed -e 's/VALUES (/VALUES (4255889,/' "INSERT INTO test (id,name) VALUES ('A');

Error: File name too long

Desire result should be

Code:
INSERT INTO test (id,name) VALUES (4255889,'A');


Also is there any way to tell shell script to ignore error and continue execution. Like

Code:
INSERT INTO test (id,name) VALUES (1,'A');
INSERT INTO test1 (id,name) VALUES (1,'A');
INSERT INTO test (id,name) VALUES (2,'A');

If test1 not exist .. script should continue and enter records for next table. I have only table in database which is test and test1 not exist. when i try to execute sql file through shell script. It gives me error that test1 table not exist and shell script terminates.

I want shell script continue execution and enter next records from sql file and ignore errors for tables which not exist in database.

Thank you.
# 6  
Old 05-15-2010
MySQL
Quote:
Originally Posted by mirfan
Hello,

None of the commands work for me. Anyways when i run following on commandline gives me error. Please let me know how to execute this

Code:
 
sed -e 's/VALUES (/VALUES (4255889,/' "INSERT INTO test (id,name) VALUES ('A');

Error: File name too long

Desire result should be

Code:
INSERT INTO test (id,name) VALUES (4255889,'A');


Also is there any way to tell shell script to ignore error and continue execution. Like

Code:
INSERT INTO test (id,name) VALUES (1,'A');
INSERT INTO test1 (id,name) VALUES (1,'A');
INSERT INTO test (id,name) VALUES (2,'A');

If test1 not exist .. script should continue and enter records for next table. I have only table in database which is test and test1 not exist. when i try to execute sql file through shell script. It gives me error that test1 table not exist and shell script terminates.

I want shell script continue execution and enter next records from sql file and ignore errors for tables which not exist in database.

Thank you.
Hi mirfan
Did you exactly like this? I m not clear..

my input must be INSERT INTO test (id,name) VALUES (4255889);
Please corret me if it is wrong

Code:
echo "INSERT INTO test (id,name) VALUES (4255889);" |
> sed "s/VALUES (4255889)/VALUES (4255889,'A')/"
INSERT INTO test (id,name) VALUES (4255889,'A');

like this
Code:
 
sed "s/my pattern that so i want to change this / change with /"

# 7  
Old 05-15-2010
Quote:
Originally Posted by mirfan
Hello,

None of the commands work for me.
What error or output did you get?

This is my output with your input file:
Code:
$ cat file
SET INSERT_ID=1
INSERT INTO test (id,name) VALUES ('a');
SET INSERT_ID=2
INSERT INTO test (id,name) VALUES ('b');
SET INSERT_ID=3
INSERT INTO test (id,name) VALUES ('c');
SET INSERT_ID=4
INSERT INTO test (id,name) VALUES ('d');
$ awk -F"[ =]" '
/^SET/{id="(" $3 ","}
/^INSERT/{sub("\\(", id ,$NF);print}
' file
INSERT INTO test (id,name) VALUES (1,'a');
INSERT INTO test (id,name) VALUES (2,'b');
INSERT INTO test (id,name) VALUES (3,'c');
INSERT INTO test (id,name) VALUES (4,'d');
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

sed for string manipulation

I have a file which contains contents like below proxy.config.cluster.mc_group_addr 224.0.1.37 proxy.config.log.logging_enabled 3 proxy.config.log.squid_log_enabled 1 Need to modify to 'proxy.config.cluster.mc_group_addr': '224.0.1.37' 'proxy.config.log.logging_enabled': '3'... (10 Replies)
Discussion started by: esham
10 Replies

2. Shell Programming and Scripting

Text manipulation with sed/awk in a bash script

Guys, I have a variable in a script that I want to transform to into something else Im hoping you guys can help. It doesn't have to use sed/awk but I figured these would be the simplest. DATE=20160120 I'd like to transform $DATE into "01-20-16" and move it into a new variable called... (8 Replies)
Discussion started by: dendenyc
8 Replies

3. Shell Programming and Scripting

Manipulation with the string using sed

hello All, When I run find command on certain directory I may get one of the following output depending on configuration A. ./rel/prod/libpam.a B. ./rel/fld/libpam.a C. ./deb/detail/libpam.a D. ./deb/err/libpam.a I want to get output as below A. rel/prod B.... (2 Replies)
Discussion started by: anand.shah
2 Replies

4. Shell Programming and Scripting

String manipulation using ksh script

Hi, I need to convert string "(joe.smith" into "joe_smith" i.e. I need to remove the leading opening brace '(' and replace the dot '.' with an under score '_' can anyone suggest a one liner ksh script or unix command for this please (3 Replies)
Discussion started by: sdj
3 Replies

5. UNIX for Advanced & Expert Users

string manipulation in bash shell

Hi All, I am using a bash shell and want to the following thing. A process sends the following string to my script BACKUP_FAIL_REASON="Failed - Application Dump CDMACA-0:grep: /opt/nortel/ca/data/1245184/sd00/image1/S110907070708HIS... (4 Replies)
Discussion started by: Pkumar Sachin
4 Replies

6. Shell Programming and Scripting

Shell scripting string manipulation

Hi, if I have a string delimited by commas how can I put each character on a new line followed by a carriage return, and output this to a filee.g from: s,t,r,i,n,g to s t r i n g thanks you (3 Replies)
Discussion started by: Wahmed9
3 Replies

7. Shell Programming and Scripting

sed string manipulation

hi I am using sed to split a string this string is 11byteabc I would like to just get the numeric digits. echo "11byteabc" | sed 's/*// returns 11byteabc only solution that works is repeating number of times for the letters which is pointless grateful for any suggestions thanks (4 Replies)
Discussion started by: speedieB
4 Replies

8. Shell Programming and Scripting

Pattern manipulation in korn shell script using sed.

Hi, Could any one let me know, how can I cut the last field in below mentioned line. net,-hopcount,0,-netmask,255.255.255.0,,,,,192.168.37.0,10.253.0.1 net,-hopcount,0,-netmask,255.255.255.0,,,,,192.168.1.0,10.253.0.1 net,-hopcount,0,-netmask,255.255.255.0,,,,,192.168.38.0,10.253.0.1... (3 Replies)
Discussion started by: ajilesh
3 Replies

9. Shell Programming and Scripting

How to use sed for string manipulation

Hi, I would like to know How to use use sed for manipulating string for the following situation. Basically my objective is to check validity of the filename in my shell script. I am getting a parameter like this for my shell script. Check my folder is having some space. $1=/root/krishna... (2 Replies)
Discussion started by: hikrishn
2 Replies

10. Shell Programming and Scripting

Need shell/sed script for grep+string replacement

Hi, Let me explain the situation. There are many files in a directory and its sub-directories that conatin the string pattern "pa". I want to replace all such instances with the pattern "pranavagarwal" doing a grep "pa" `ls` does give me all the instances of the occurence of that... (3 Replies)
Discussion started by: pranavagarwal
3 Replies
Login or Register to Ask a Question