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
- Getting Old
- SQL Server 2005 file sizes and filegrowth
- Georgian Manor, hard sells and pyramid schemes
- Data Obfuscation through Random String replacement in SQL Server
- How long does it take to become a good writer?
- Deciding against Code Collapse in SQL Server Management Studio 2008
- Laurier in Chain e-mails on Immigration
- Junior Rangers
Tag! You’re it!2008 age Apocalypse art Atheism bicycling Biology book Canadian cards Christmas climate change comedy conservation cycling database Disney documentary environment environmental awareness Excel fantasy film game 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