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
- Data Obfuscation through Random String replacement in SQL Server
- Laurier in Chain e-mails on Immigration
- SQL Server 2005 file sizes and filegrowth
- Georgian Manor, hard sells and pyramid schemes
- Creating a SQL Server database by script
- Boy, Roald Dahl and early Sensory Science
- Data import/export (DTS) on SQL Server Express (SSE)
- The Prestige
Tag! You’re it!2008 age Apocalypse art backup bicycling Biology book Canadian cards Christmas comedy conservation cycling database Disney documentary environment environmental awareness Excel fantasy film game global warming graphic novel graphic novels Guelph Guelph Festival of Moving Media Guelph International Film Festival haiku hiking history humour Jane Austen Kazu Kibuishi long distance love Microsoft Montreal movie music mystery native Nature Neil Gaiman novel Old Growth Forest Oshawa performance podcast Poem poetry Poetry Month quote rain recipe review Robert J. Sawyer science fiction short story silly snow Space opera speculative fiction Spring SQL SQL Server SQL Server 2005 Stephen King story The Education of Mike Moonblazer touring TV water writing