People often wonder how they can search and replace data stored in their tables. While this doesn't sound like a very good thing to do, there are some genuine situations where such functionality is needed. For example, spelling mistakes in the column values come to mind, as do moving a database from one location to another or wanting to replace all location-specific content.
Is there a built-in 'Global Search and Replace' functionality available in SQL Server? No. The REPLACE function provided by T-SQL can replace a given string in a string variable or a column, but you cannot directly use the REPLACE function to loop through all character columns of all tables to globally replace a string. This needs some programming effort.
Narayana Vyas Kondreddi offers a stored procedure named SearchAndReplace that searches through all the character columns of all tables in the current database and replaces the given string with another user-provided string. It accepts a search string and a replace string as input parameters; goes and searches all char, varchar, nchar, nvarchar columns of all tables (only user-created tables -- system tables are excluded) owned by all users in the current database and replaces all occurences of the search string with the replace string.
The article continues at
Back to Database Journal Home