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:

view plain print about
1update my_table
2set 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.

view plain print about
1update my_table
2set 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:

view plain print about
1update tblBlogEntries
2set body = cast(replace(cast(body as nvarchar(max)), 'http://www.jeffryhouser.com', 'https://www.jeffryhouser.com') as ntext)
3where body like '%http://www.jeffryhouser.com%'
5update tblBlogEntries
6set morebody = cast (replace(cast(morebody as nvarchar(max)), 'http://www.jeffryhouser.com', 'https://www.jeffryhouser.com') as ntext)
7where morebody like '%http://www.jeffryhouser.com%'

I hope this helps someone.

Sign up for DotComIt's Monthly Technical Newsletter