I run a Ghost blog and I want to find and replace all my single and double quotes into "curly quotes," (see here) but I don't know SQLite very well. So far, I have come up with this:
update posts set markdown = replace(markdown, '""', '“');
The above command replaces all my double quotes to opening curly quotes (ALT+0147). Then I tried running the same command, but substituting for the closing quote (ALT+0148), like so:
update posts set markdown = replace(markdown, '“', '”');
Which basically replaces all the opening curly quotes with closing quotes. And that is where I am stuck. I'm not sure how to make an exception in sqlite, so that it only replaces the quotes that have text after them. Additionally, I haven't considered cases, where I want my straight quotes to stay the same, such as in the HTML links, as pointed out in the comments.
Update: It Works
Thanks to an answer, I have found a solution, which in my case has worked perfectly! Take note that I used the DB Browser for SQLite to make changes to my database -
update posts set markdown = replace(markdown, ' "', ' “');
update posts set markdown = replace(markdown, '." ', '.” ');
update posts set markdown = replace(markdown, '" ', '” ');
The above two commands have replaced all the opening double quotes with curly quotes, and the same for the closing quotes. All that's now left to do is, as stated in the answer below, cover all the possibilities, where such quotes may appear. Same goes for the single quotes, as so:
update posts set markdown = replace(markdown, ' ''', ' ‘');
update posts set markdown = replace(markdown, '.'' ', '.’ ');
update posts set markdown = replace(markdown, ''' ', '’ ');
And for all the inbetween apostrophes:
update posts set markdown = replace(markdown, '''', '’');
SQLite (in the base version I know) is not very powerful in text manipulation. And even with things like sed, awk or perl it would probably not be easy to get a single replacement doing what OP wants.
I will provide some pure SQLite recipes, which need to be extended and combined to cover all desired cases (and not the undesired cases).
Finding and covering all cases is admittedly a bit tedious. But this is the best I can think of within SQLite. I am happy to upvote any simpler, more powerful answer.
You need to apply those recipes you like to your database.
It should also be easy to extend them to cover cases I have not thought of.
I recommend to first try the effect of each of them by only using the bare recipe, that will just show the "markdown" column in changed form. Only if you like its effect on all your entries, you should apply it, using the "update/set" thing. (I am relying on you being able to do that, your quote of your own attempt looks good.)
Just to explain; applying recipe 1 and 2 will cover the one example I spotted in your sample input, the "What. A. FILM.".
In chat you have (rather flatteringly, thanks) indicated that the other recipes are "reading your mind". Spotting the pattern and extending it to any other example should not be hard.
The one thing I do not see a solution in SQLite for would be 'I repeat " WHAT A FILM " blabla.' The problem are the spaces between the quotes and the quoted text. In sed/perl/awk solutions could check for "first/odd straight quote" and "second/even straight quote" but SQLite cannot easily do that, maybe not at all, ideas are welcome.
1 Replace "space followed by straight double quote" by "space followed by opening curly":
select replace(markdown, ' "', ' “') from posts;
2 Replace "straight double quote followed by space" by "closing curly followed by space":
select replace(markdown, '" ', '” ') from posts;
3 Replace "straight double quote followed by comma" by "closing curly followed by comma":
select replace(markdown, '",', '”,') from posts;
4 Replace "straight double quote followed by semicolon" by "closing curly followed by semicolon":
select replace(markdown, '";', '”;') from posts;
5 Replace "straight double quote followed by fullstop" by "closing curly followed by fullstop":
select replace(markdown, '".', '”.') from posts;
Do the same thing for opening and closing single quotes.
When you start inventing your own replacements, make sure NOT to cover the http link and NOT to cover the '', both of which seem obviously not your intended replacement target.
Note, I explicitly admit that these replacements have failed on my own machine. But in a chat, OP has tried the simplest version and confirmed that for him they succeed. The main difference seems to be that he uses a database browser.
I consider these things therefor "tested successfully", just not on my machine.