Search and Replace in SQL Server

And now a chance to wax programmatic about the replace feature in SQL Server. Opening up a table and editing thousands of cells can be a nightmare and error prone. The replace fuction can be used in an update statement to do useful searching and replacing. Quickly. I learned how to do this using BOL (Books On Line) and an article by Bill Graziano on the SQL Team site.
So say we’ve got a table called tbldata with several fields in it including an nvarchar field called ‘strdata’. I want to remove the string ‘nasty ‘ completely. I can do so like this:
update tbldata set strdata = replace(strdata, 'nasty ', '')
And if I want to fix a typo:
update tbldata set strdata = replace(strdata, 'mistaek', 'mistake')
This will, of course, change all occurences so if you want to get fancy you’ll need a where clause.
Whether this find and replace search changes all cases or not will depend on your collation which can be set at the database, table and even field level. So that is something else to bear in mind if you’re concerned about exact matching. In my case I usually set my databases to be:
COLLATE SQL_Latin1_General_CP1_CI_AS
which means that the search is case insensitive (you can tell since the second last initials say ‘CI’; case sensitive collations would be ‘CS’).

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s