SED + Regex + SQL Input file

Shell Programming and Scripting
1

Here's the problem...

I have a mysqldump file and I need to put single quotes around the date/time timestamp. So for example I have a line like:

INSERT INTO attachments VALUES (1,182,2004-08-06 09:24:04,'description'...

and I need it to become

INSERT INTO attachments VALUES (1,182,'2004-08-06 09:24:04','description'...

I tried

cat backup.sql | sed s/\,200/,\'200/ | sed s/..:..:../\&\'/ | less

and it did what I wanted, however it would also add a single quote infront of the first occurance of 200 so eventually it would insert

('2005,20041,2003-05-06 10:13:12',...

So then I attempted

cat backup.sql | sed s/.*\,.*\,/\&\'/ | sed s/..:..:../\&\'/ | less

but it seems to only add the single quote after the time. I can't figure out how to get the single quote before the date as well.

Anyone able to provide assistance?

Thanks!
Primal

2

Try this:

awk -F, '/INSERT INTO attachments VALUES/{$3="\047"$3"\047"}1' backup.sql

Regards

3

Try:

sed "s/,\(200[0-9]-..-.. ..:..:..\)/,'\1'/" mysqldump
4

@unilover
When I run your command it returns:

\1 not defined in the RE

@Franklin52
When I run your command it places the single quotes around the dates but it removed the commas

...VALUES (1 182 '2004-...

I was able to get it with this

sed s/VALUES\ \([0-9]*\,[0-9]*\,/\&\'/ | sed s/..:..:../\&\'/

but then I realized that the timestamp also appears in other tables (different fields and more than once)

This is where I am right now

sed s/....-..-..\ ..:..:../\'&\'/g

The only problem with that is some timestamps already have single quotes around them and now after this line, some timestamps get 2 singles quotes surrounding them ''2004-08-06 09:24:04''

Is there anyway to do it so that it skips any timestamps that already have quotes?

I can do

s/,....-..-..\ ..:..:../\'&\'/g

but that would place the single quote ',2004-08-06 09:24:04' Is it possible to include the comma in the search and then place the single quote right after it?

5

Maybe if you append the following to your command-line:

|sed -e "s/''2/'2/g" -e "s/'',/',/g"
6 
awk 'BEGIN{FS=OFS=","}/INSERT INTO attachmentsVALUES/{$3="\047"$3"\047"}1' backup.sql

Regards

7

@Franklin52
Can you explain your awk command? What does the 1 at the end mean?

8

awk evaluates that as true and the default action is to print the whole line, similar to:

{print $0}

Regards

9

Ok, so awk seems to be the way to go here. I've used it twice already in the sql file, but the 3rd time is a bit different. Same deal as before, I need to put single quotes around the date/time

So, change this

INSERT INTO logincookies VALUES (3229,47,'201.208.41.125',2008-03-12 17:42:49);

to

INSERT INTO logincookies VALUES (3229,47,'201.208.41.125','2008-03-12 17:42:49');

When I use this command

awk 'BEGIN{FS=OFS=","}/INSERT INTO logincookies VALUES/{$4="\047"$4"\047"}1' backup.sql

it puts the single quotes

INSERT INTO logincookies VALUES (3229,47,'201.208.41.125','2008-03-12 17:42:49);'
10 
awk '
BEGIN{FS=OFS=","}
/INSERT INTO logincookies VALUES/{$4="\047"substr($4,1,19)"\047"substr($4,20)}
1'  backup.sql

Regards

11

Thanks for your help Franklin52 and unilover.

I ended up using a mixture of both commands (simple replacements done with SED, others with AWK) to fix my sql file.