Escape Single Quote Characters when Importing to SQLite

Question

Hi everyone. I have a records.txt file like this:

INSERT INTO 'blogtitles' VALUES('Kelly's at house');
INSERT INTO 'blogtitles' VALUES('Nice catch!');

When i try to import records.txt into the db: sqlite3 my.db < records.txt It gives an error because of the first line. I have many lines like this in the records.txt file. I need a sed syntax that will escape all these single quotes within the strings. So there will not be any problem when importing. I really need this :( Thank you!

Answer 1

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).