Sed command does not recognise apostrophes

Question

In my bash script, I am trying to replace all apostrophes(') with two apostrophes('') in a csv file in order to commit it to a postgres database. As you know all single quotes in a postgres query need to be escaped with an apostrophe.

I can't use double quotations around my variables in my query because they also have double quotes in them too. So there is no easy way out apart from doing a blanket replace using sed. I have experimented with the following ways but to no avail:

sed "s/\'/\'\'/g" test.txt   #Does not work

sed "s/'/''/g" test.txt      #Does not work  

sed s/\'/"\'\'"/g test.txt   #Does not work  

Does anyone have ideas on how I can get this to work?

Answer 1

This will work for you:

sed "s/'/''/g"  <<< "test'test"

Since you are using double quotes to enclose the command, the apostrophe doesn't need to get escaped.

Btw, if you want to use single quotes to enclose the command itself, you may use the octal representation of the apostrophe \047 in the command:

sed 's#\047#\047\x47#g' <<< "test'test"

Answer 2

This might work for you (GNU sed):

sed 's/'\''/&&/g' file

The second ' ends the quoting of the first part of the substitute command. The third ' is in the shell and is quoted thus \'. The remainder of the substitution command is then quoted. Using the && as shorthand for entire match in the LHS of the substitutin command doubled up.

N.B. It is always preferable to use single quotes to quote sed commands as it removes the chance of the shell accidentally interpreting characters in the command.

Answer 3

Since you indicated:

I can't use double quotations

you might wish to consider using $'...' if your shell allows it (sh, ksh, bash and others do):

sed $'s/\'/\'\'/g'