Perform find replace in SQL update staement

I had an interesting problem today where I needed to update strings in a database table field based on a certain criteria.

A bad character had gotten into the URL field in the rhpRef table. The SQL updates and string that matches ‘warehouse-’ with ‘warehouse’. Ive added the where clause just to ensure only the correct rows are updated.

Here’s the code for future reference:

UPDATE rhpRef
SET    url = replace(url, 'warehouse-', 'warehouse')
WHERE  url LIKE '%warehouse-%';

Hope it might help someone else.

Like this:

Be the first to like this post.

This entry was posted on 4 January, 2011 at 2:54 pm and is filed under SQL Server, Stored Procedures. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.