Escape Single Quote Characters when Importing to SQLite

The SQL standard specifies that single-quotes in strings are escaped by putting two single quotes in a row. SQL works like the Pascal programming language in the regard. SQLite follows this standard. Example:

INSERT INTO xyz VALUES('5 O''clock');

link

You can't do that with regular expressions in the general case because they can't count. But if the file looks just like that, you can fake it:

sed -e "s/INSERT INTO 'blogtitles' VALUES('//" -e "s/');//" \     -e "s/'/''/g" \     -e "s/^/^INSERT INTO 'blogtitles' VALUES('/" -e "s/$/');/"

i.e. remove the static part of the line, duplicate the quotes and then attach the static part again.

If your example is too simple, I suggest to have a look at gawk(1) which can do much more complicated processing (for example, split the line at "','" which is probably between two values and nowhere else).

That's what I often use :

cat myfile \| sed -e "s/X/XX/g" \| sed -e "s/^\([^']*\)'\([^']\)*'\([^']\)*'/\1XQ\2XQ\3XQ/" \| sed -e "s/'\([^']*\)$/XQ\1" \| sed -e "s/'/''/g" \| sed -e "s/XQ/'/g" \| sed -e "s/XX/X/g"

The main advantage compared to Aaron's answer is that even if the begining of the line isn't always the same, it still works, although to the expense of longer code.

It's easy to remember :

• Choose an 'escape character'
• Escape it itself
• Escape the occurrences of your character that you want to keep (but don't let the original character appear in the escaped sequence)
• Transform the occurrences of your character
• Unescape your character
• Unescape the escape character

Or, in short :

• Escape everything execpt what you need to transform
• Transform
• Unescape everything