how to do a search and replace for a string in mssql 2012

Question

I am having to find and replace a substring over all columns in all tables in a given database.

I tried this code from sqlserver 2012 ssms but resulting in errors from http://www.dbtalks.com/uploadfile/anjudidi/find-and-replace-string-values-in-all-tables-and-column-in-s/ Find and Replace string Values in All Tables and column in SQL Serve

I think its for older version, it having problems with some of the tables names that start with a number: example dbo.123myTable

Appreciate all the help in advance

Error Print:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '.153'. UPDATE dbo.153Test2dev SET [ALCDescription] = REPLACE(convert(nvarchar(max),[ALCDescription]),'TestsMT','Glan') WHERE [ALCDescription] LIKE '%SherlinMT%' Updated: 1

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '.153'. UPDATE dbo.153TypeTest2 SET [FormTypeDescription] = REPLACE(convert(nvarchar(max),[FormTypeDescription]),'TestsMT','Glan') WHERE [FormTypeDescription] LIKE '%SherlinMT%' Updated: 1

score 0 · Answer 1 · answered May 05 '14 at 17:31

0

Are you sure table names may begin with a digit? If so, include them in '[' ']', like

UPDATE [dbo].[153TypeTest2].....

answered May 05 '14 at 17:31

alzaimar

4,572
1
16
30

score 0 · Accepted Answer · answered May 05 '14 at 17:32

Just as a guess, to add delimiters to your table names, modify the script you linked to by editing this line:

    SET @sqlCommand = 'UPDATE ' + @schema + '.' + @table + ' SET [' + @columnName + '] = REPLACE(convert(nvarchar(max),[' + @columnName + ']),''' + @stringToFind + ''',''' + @stringToReplace + ''')'

and change it to

  SET @sqlCommand = 'UPDATE [' + @schema + '].[' + @table + '] SET [' + @columnName + '] = REPLACE(convert(nvarchar(max),[' + @columnName + ']),''' + @stringToFind + ''',''' + @stringToReplace + ''')'

score 0 · Answer 3 · answered May 05 '14 at 17:32

0

based in the code you linked to, try this:

SET @sqlCommand = 'UPDATE [' + @schema + '].[' + @table + '] SET [' + .....
--add square braces:      ^               ^ ^              ^

answered May 05 '14 at 17:32

KM.

101,727
34
178
212

score 0 · Answer 4 · answered May 05 '14 at 17:50

You should bracket your table name in the same way as the column name in the update query (see @table now has brackets):

SET @sqlCommand = 'UPDATE ' + @schema + '.[' + @table + '] SET [' + @columnName + '] = 
REPLACE(convert(nvarchar(max),[' + @columnName + '])

how to do a search and replace for a string in mssql 2012

4 Answers4