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
# 8  
Old 05-18-2010
Hello,

Both of the solution(ygemici & Franklin52) works for simple queries metioned earlier.
But i have very long queries sql file contains 1000 of database queries. e.g.

cat sourcefile.sql
Code:
SET INSERT_ID=1;
INSERT INTO table1 (column1,column2..column34) VALUES (column2value..column34value);

SET INSERT_ID=2;
INSERT INTO table2 (column1,column2..column23) VALUES (column2value..column23value);

This should be converted into as follows outfile.sql, where column1value is SET INSERT_ID
Code:
INSERT INTO table1 (column1,column2..column34) VALUES (column1value,column2value..column34value);

INSERT INTO table2 (column1,column2..column23) VALUES (column1value,column2value..column23value);

When i tried Franklin52 solution doesn't seem to work on mentioned queries because every table in sourcefile.sql contains different tables and no. of columns.

While ygemici your solution works from command line but when i run from shell script it gives me error "Filename too long".

Here is my shell script
Code:
# !/bin/bash

IDS=`cat sourcefile.sql | grep INSERT_ID | awk -F"=" '{print $2}' | sed 's/\(.*\)....../\1/'`    # all INSERT_IDS are fetched in array
LINES=$(cat sourcefile.sql | wc -l) # total no. of lines in sourcefile.sql

for i in $ID
do
        for (( i=2; i<=$LINES; i=$i+2 ))
        do
        QUERY=`head -$i sourcefile.sql | tail -1`
        MQUERY=`echo 'echo' "\"$QUERY ($f);\"|"`
        SUB=`echo "sed \"s/VALUES (/VALUES ($f,/\""`
        FINALQ=`"echo \"$QUERY\" | $SUB"`
        eval $FINALQ   # gives error "Filename too long"
        done
done

Last edited by Franklin52; 05-18-2010 at 06:26 AM.. Reason: Correcting code tags
# 9  
Old 05-18-2010
If you don't have spaces between the values this should work:
Code:
awk -F"[ =]" '
/^SET/{id="(" int($3) ","}
/^INSERT/{sub("\\(", id ,$NF);print}
' file

Otherwise:
Code:
awk -F"(" '
/^SET/{sub(".*=",""); id=int($0) ","}
/^INSERT/{$3=id $3;print}
' OFS="(" file

# 10  
Old 05-18-2010
Hi,

Thanks great. second solution for me.
Thanks for the help

Thank you.
# 11  
Old 05-18-2010
MySQL
Then
for example let my file is like below

Code:
 # cat mysql
SET INSERT_ID=1;
INSERT INTO table1 (column1,column2..column34) VALUES (column2value..column34value);

SET INSERT_ID=2;
INSERT INTO table2 (column1,column2..column23) VALUES (column2value..column23value);

SET INSERT_ID=3;
INSERT INTO table3 (column1,column2..column34) VALUES (column2value..column34value);

SET INSERT_ID=4;
INSERT INTO table4 (column1,column2..column34) VALUES (column2value..column34value);

SET INSERT_ID=5;
INSERT INTO table5 (column1,column2..column34) VALUES (column2value..column34value);

SET INSERT_ID=6;
INSERT INTO table6 (column1,column2..column34) VALUES (column2value..column34value);

Code:
 # ./changex
INSERT INTO table1 (column1,column2..column34) VALUES (1,column2value..column34value);
INSERT INTO table2 (column1,column2..column23) VALUES (2,column2value..column23value);
INSERT INTO table3 (column1,column2..column34) VALUES (3,column2value..column34value);
INSERT INTO table4 (column1,column2..column34) VALUES (4,column2value..column34value);
INSERT INTO table5 (column1,column2..column34) VALUES (5,column2value..column34value);
INSERT INTO table6 (column1,column2..column34) VALUES (6,column2value..column34value);

Code:
 # cat changex
 
#!/bin/bash
id=$( (sed -n '/=/p' mysql | sed -e 's/SET INSERT_ID=//' -e 's/;//') ) ; idgr=( $id )
idcount=`echo $id | tr -d [:space:]` ; count=${#idcount}
sed -e '/SET INSERT_ID/d' -e '/^$/d' mysql > mysqltmp
rm -f mysqlnew
for ix in ${idgr[@]}
   do
     sed -n "/INSERT.*table$ix.*VALUES/s/\(column2value.*\));$/$ix,\1);/p" mysqltmp >> mysqlnew
   done
cat mysqlnew

# 12  
Old 05-18-2010
Taken from the original poster's two data samples:
Code:
$ cat data
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');
SET INSERT_ID=5;
INSERT INTO table1 (column1,column2..column34) VALUES (column2value..column34value);

SET INSERT_ID=6;
INSERT INTO table2 (column1,column2..column23) VALUES (column2value..column23value);


Relatively simple sed solution:
Code:
$ sed '/^SET INSERT_ID=/{s/;*$//;h;d;}; /VALUES/{G;s/VALUES (\(.*\)\(\nSET INSERT_ID=\)\(.*\)/VALUES (\3,\1/;}' data
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');
INSERT INTO table1 (column1,column2..column34) VALUES (5,column2value..column34value);

INSERT INTO table2 (column1,column2..column23) VALUES (6,column2value..column23value);

Regards,
Alister
# 13  
Old 05-19-2010
Great. All of the solutions worked for me.
Now finally i had some table data as follows in input file.
Code:
SET INSERT_ID=1;
INSERT INTO table1 (column1,column2..column34) VALUES (column2value..column34value),(column2value..column34value),(column2value..column34value);

SET INSERT_ID=2;
INSERT INTO table2 (column1,column2..column23) VALUES (column2value..column23value),(column2value..column23value);

I need output file as INSERT_ID should be inserted at where column value starts after comma (,).

Code:
INSERT INTO table1 (column1,column2..column34) VALUES (INSERT_ID,column2value..column34value),(INSERT_ID,column2value..column34value),(INSERT_ID,column2value..column34value);
here INSERT_ID is 1

INSERT INTO table2 (column1,column2..column23) VALUES (INSERT_ID,column2value..column23value),(INSERT_ID,column2value..column23value);
and here INSERT_ID is 2

Thanks in advance.
Last edited by Franklin52; 05-19-2010 at 08:37 AM.. Reason: Correcting code tags
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