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:
which means that the search is case insensitive (you can tell since the second last initials say ‘CI’; case sensitive collations would be ‘CS’).
If wishes were horses
Golbing Time Dimension
- Using Excel to generate Inserts for SQL Server
- Junior Rangers
- Something Greater than me
- Northwest of 1721's Kodiak
- Data Obfuscation through Random String replacement in SQL Server
- The Tale of the Bunny on Paul Avenue
- Sunday afternoon at Camp Restall
- SQL Server Performance and the IN clause
- Creating a SQL Server database by script
- Georgian Manor, hard sells and pyramid schemes
Tag! You’re it!2008 age Apocalypse art Atheism bicycling Biology book Canadian cards Christmas comedy conservation cycling database Disney documentary environment environmental awareness Excel fantasy film game global warming graphic novel Guelph Guelph Festival of Moving Media Guelph International Film Festival haiku hiking history humour Jane Austen long distance love Microsoft Montreal movie music mystery native Nature Neil Gaiman novel Old Growth Forest Oshawa performance philosophy Poem poetry Poetry Month quote recipe relationship review Robert J. Sawyer science fiction short story silliness silly snow society Space opera speculative fiction Spring SQL SQL Server SQL Server 2005 Stephen King story The Education of Mike Moonblazer touring TV water writing