Check out our Angular Book Series.

How do I do a search and replace in SQL?

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:


update my_table
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.


update my_table
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:


update tblBlogEntries
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%'

update tblBlogEntries
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.

Sign up for DotComIt's Monthly Technical Newsletter

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
All Content Copyright 2005, 2006, 2007, 2008, 2009 Jeffry Houser. May not be reused without permission
BlogCFC was created by Raymond Camden. This blog is running version 5.9.2.002.