I recently upgraded this blog to run exclusively on SSL. The first thing I noticed is that many of the links and image references inside blog posts were not HTTPS and the browser would show one of those 'this page is not secure errors'. The reason that the text of my posts use absolute links instead of relative is so that they'll show up properly for those who consume the blog via RSS and read it elsewhere.
I spent a few hours white boarding a bubble sort algorithm before deciding I was getting nowhere, I decided to try a Google Search for the answer.
In SQL Server, which is my blog's data store, you can use a replace function to make the change. I can do a simple search for text inside a column and replace it with something else. Perfect, that is what I need.
Here is a StackOverflow answer with more details. The query they recommend is:
set column = replace(column,'oldstring', 'newstring')
Of course, when I did that I immediate got a conversion error. The body of these posts are stored as ntext fields inside the database. and the replace() function will not work on ntext or text fields. Thankfully, there is a solution to that too. A simple cast addressed the issue.
set column = cast(replace(cast(column as nvarchar(max)),'oldstring', 'newstring') as ntext)
If you use BlogCFC and need to do this on one of your blogs, here is the final script I used:
set body = cast(replace(cast(body as nvarchar(max)), 'http://www.jeffryhouser.com', 'https://www.jeffryhouser.com') as ntext)
where body like '%http://www.jeffryhouser.com%'
set morebody = cast (replace(cast(morebody as nvarchar(max)), 'http://www.jeffryhouser.com', 'https://www.jeffryhouser.com') as ntext)
where morebody like '%http://www.jeffryhouser.com%'
I hope this helps someone.