CODEWITHSUNDEEP
regexsed
William Cave
William Cave

Reputation: 13

Regex replacement for SQL using sed

I have a file containing many SQL statements and need to add escape characters, using SED, for single quotes withing the SQL statements. Consider the following:

INSERT INTO MYTABLE VALUES (1,'some text','Drink at O'Briens');

In the above we need to escape the single quote in O'Briens. Using regex I can find the string using [a-zA-Z ]'[a-zA-Z ].

So this will find the 3 characters of interest, however if I do the following sed command:

sed -i "s/[a-zA-Z ]'[a-zA-Z ]/''/g" file.sql

This, however, removes the O and the B so I end up with:

INSERT INTO MYTABLE VALUES (1,'some text','Drink at ''riens');

How do I isolate/reference the O and the B so the string becomes:

INSERT INTO MYTABLE VALUES (1,'some text','Drink at O''Briens');

Upvotes: 1

Views: 58

Answers (2)

RavinderSingh13
RavinderSingh13

Reputation: 133528

You could do this in awk. Simple explanation would be, perform substitution on last field of line, where substitute ' with 2 instances of ' and print the line then.

awk '{sub(/\047/,"&&",$NF)} 1' Input_file

Above code will only print the lines in output, in case you want to perform inplace save then try following.

awk '{sub(/\047/,"&&",$NF)} 1' Input_file > temp && mv temp Input_file

Upvotes: 1

Barmar
Barmar

Reputation: 781078

Use capture groups to copy parts of the input to the result.

sed -r -i "s/([a-zA-Z ])'([a-zA-Z ])/\1''\2/g" file.sql

Upvotes: 1

Related Questions