Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

Database News

Posted Nov 6, 2002

Search and Replace SQL Server Data Across Tables

By DatabaseJournal.com Staff

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 http://vyaskn.tripod.com/sql_server_search_and_replace.htm.

Back to Database Journal Home

Database News Archives

Latest Forum Threads
Database News Forum
Topic By Replies Updated
Efficient SQL Server Indexing by Design lcole 0 April 30th, 12:38 PM
Mine Oracle Database, SQL Server and Other Databases with Monarch Data Pump Pro V10.5 lcole 0 April 30th, 12:37 PM
Oracle Database and Oracle Fusion Middleware for Private Social Network Application lcole 0 April 30th, 12:31 PM
Oracle Database Maintains a Stronghold in the DBMS Market lcole 0 April 30th, 12:30 PM