Learn more about SQL Server tools

 
 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

SQL Server Find and Replace Values in All Tables and All Text Columns


By:   |   Read Comments (27)   |   Related Tips: More > Scripts

Problem
In a previous tip, Searching and finding a string value in all columns in a SQL Server table, you showed how to find a string value in any text column in any table in a database.  I was wondering how this can be taken a step further to allow a replacement of text in a string data type for all columns and tables in my database.  I have read about a SQL injection attack where text is inserted and this could be a good way to remove the offending text.

Solution
The first tip included a script that could be run to find a certain string in any text data type such as char, nchar, nvarchar, varchar, text and ntext. 

The script for this tip basically follows the same premise to find the data, but takes it a step further and allows you to replace the text that is found.

The only thing that needs to change to run this script are the database where you want this to run and the values for these two parameters:

  • @stringToFind
  • @stringToReplace
SET NOCOUNT ON

DECLARE 
@stringToFind VARCHAR(100)
DECLARE @stringToReplace VARCHAR(100)
DECLARE @schema sysname
DECLARE @table 
sysname
DECLARE @count INT
DECLARE 
@sqlCommand VARCHAR(8000)
DECLARE @where VARCHAR(8000)
DECLARE @columnName 
sysname
DECLARE @object_id INT
                    
SET 
@stringToFind 'Smith'
SET @stringToReplace 'Jones'
                       
DECLARE TAB_CURSOR CURSOR  FOR
SELECT   
B.NAME      AS SCHEMANAME,
         
A.NAME      AS TABLENAME,
         
A.OBJECT_ID
FROM     sys.objects A
         
INNER JOIN sys.schemas B
           
ON A.SCHEMA_ID B.SCHEMA_ID
WHERE    TYPE 'U'
ORDER BY 1
         
OPEN TAB_CURSOR

FETCH NEXT FROM TAB_CURSOR
INTO @schema,
     
@table,
     
@object_id
     
WHILE @@FETCH_STATUS 0
  
BEGIN
    DECLARE 
COL_CURSOR CURSOR FOR
    SELECT 
A.NAME
    
FROM   sys.columns A
           
INNER JOIN sys.types B
             
ON A.SYSTEM_TYPE_ID B.SYSTEM_TYPE_ID
    
WHERE  OBJECT_ID @object_id
           
AND IS_COMPUTED 0
           
AND B.NAME IN ('char','nchar','nvarchar','varchar','text','ntext')

    
OPEN COL_CURSOR
    
    
FETCH NEXT FROM COL_CURSOR
    
INTO @columnName
    
    
WHILE @@FETCH_STATUS 0
      
BEGIN
        SET 
@sqlCommand 'UPDATE ' @schema '.' @table ' SET [' @columnName
                           + '] = REPLACE(convert(nvarchar(max),[' @columnName ']),'''
                          
@stringToFind ''',''' @stringToReplace ''')'
        
        
SET @where ' WHERE [' @columnName '] LIKE ''%' @stringToFind '%'''
        
        
EXEC@sqlCommand @where)
        
        
SET @count @@ROWCOUNT
        
        
IF @count 0
          
BEGIN
            PRINT 
@sqlCommand @where
            
PRINT 'Updated: ' CONVERT(VARCHAR(10),@count)
            
PRINT '----------------------------------------------------'
          
END
        
        FETCH 
NEXT FROM COL_CURSOR
        
INTO @columnName
      
END
    
    CLOSE 
COL_CURSOR
    
DEALLOCATE COL_CURSOR
    
    
FETCH NEXT FROM TAB_CURSOR
    
INTO @schema,
         
@table,
         
@object_id
  
END
  
CLOSE 
TAB_CURSOR
DEALLOCATE TAB_CURSOR

If the above is run in the AdventureWorks database as is, these are the messages that are returned.

UPDATE Person.Address SET [AddressLine1] = REPLACE(convert(nvarchar(max),[AddressLine1]),'Smith','Jones') WHERE [AddressLine1] LIKE '%Smith%'
Updated: 2
----------------------------------------------------
UPDATE Person.Address SET [City] = REPLACE(convert(nvarchar(max),[City]),'Smith','Jones') WHERE [City] LIKE '%Smith%'
Updated: 1
----------------------------------------------------
UPDATE Person.Contact SET [LastName] = REPLACE(convert(nvarchar(max),[LastName]),'Smith','Jones') WHERE [LastName] LIKE '%Smith%'
Updated: 105
----------------------------------------------------
UPDATE Production.ProductReview SET [ReviewerName] = REPLACE(convert(nvarchar(max),[ReviewerName]),'Smith','Jones') WHERE [ReviewerName] LIKE '%Smith%'
Updated: 1
----------------------------------------------------

The above shows the command that was run and how many rows were affected.  As you can see we are using the CONVERT function to convert the datatypes to nvarchar(max) prior to doing the REPLACE function.  The reason for this is that you can not use the REPLACE function against a text or ntext datatype, so we are doing a conversion prior to the change.  Although the CONVERT is not needed for char, nchar, varchar and nvarchar it was easier to just convert everything instead of having different logic, but this could be easily put in place.

If we did not use the CONVERT function we would have to use these two functions TEXTPTR and UPDATETEXT to change the data in the text and ntext columns.  This is a lot more work and therefore the approach we used is much simpler.  The downside is that this only works for SQL 2005 and later where the nvarhcar(max) datatype is supported.  In addition, this is another reason that Microsoft suggests moving away from text and ntext to varchar(max) and nvarchar(max).

One thing to note is that if your replacement text is longer than the text your are searching for you may run into issues of truncating data which is not handled in this script.

Next Steps

  • Depending on the size of your database this could take some time to run, so be careful when you run this since it will be hitting every table and every column that has one of these datatypes: char, nchar, nvarchar, varchar, text and ntext.
  • Give it a try and see how it works.  You can use the BEGIN TRAN and ROLLBACK statements to see what will be updated and than rollback the transactions.  Just be careful on large databases and production databases since this will hold locks on the UPDATEs until the rollback statement is issued.
  • If you have any tweaks to offer, please submit them to the forum post mentioned below.


Last Update:





About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips





More SQL Server Solutions




Post a comment or let the author know this tip helped you.

All comments are reviewed, so stay on subject or we may delete your comment.

*Name    *Email    Notify for updates 

Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Tuesday, March 24, 2015 - 3:20:38 PM - Regginald Kembo Back To Top

Thanks so much for this. You've jsut saved me a lot of time deleting spam links in my DB.


Tuesday, July 29, 2014 - 1:58:52 PM - Kyle S Back To Top

We had a table named "User" so we had to change this line:

 

'UPDATE ' @schema '.' @table ' SET [' @columnName 

to

'UPDATE ' @schema '.[' @table '] SET [' @columnName  

 

We needed to add those [ ] characters around the table.

Thanks.


Wednesday, March 05, 2014 - 9:16:02 AM - Tim Back To Top

Awesome!  Thank you.

For those wanted to run this against one table, first select your DB (add USE YOURDBNAME GO to the begining of the script), and then in the first select statement towards the top, change

WHERE TYPE = 'U'

to 

WHERE TYPE = 'U' AND a.NAME = 'YOURTABLENAME'

 

Cheers,

Tim

 


Sunday, July 21, 2013 - 12:05:05 AM - JasonB Back To Top

Okay. I give. How do you modify this to find/replace in a single table, rather than all of my tables across my whole db?

Thanks for the great script.


Friday, May 24, 2013 - 9:48:06 AM - FrogMeister Back To Top

This worked beautifully!  You have saved me so much time and frustration I can't thank you enough! K-


Friday, March 15, 2013 - 11:57:22 AM - Marc Back To Top

Thank you so much, this just saved me so much time - I ran it without errors on Microsoft SQL 2008 R2 to Search & Replace an email address that was in multiple tables and columns.


Thursday, January 10, 2013 - 10:05:51 AM - Greg Robidoux Back To Top

@Nicholas - no this script won't allow you to make the datatype changes.


Tuesday, January 08, 2013 - 5:47:22 PM - Nicholas Petersen Back To Top

Actually, now that I think about it, I'd probably have to drop and recreate all tables that I wanted to do that to.


Tuesday, January 08, 2013 - 5:45:09 PM - Nicholas Petersen Back To Top

Can this script be run to change the data type in all tables, for example, from varchar(max) to text?


Wednesday, November 21, 2012 - 9:47:09 AM - Greg Robidoux Back To Top

@Girish - you should be able to use the above or just use a simple REPLACE statement

REPLACE(@string,'##test1##','RealValue')


Wednesday, November 21, 2012 - 7:29:03 AM - Girish Rakhe Back To Top

I have a  string  

for ex. 'Test Values ##test1## testing value ##t1##' 

 

Now what i want i want to replace all values from string which coming under ## values 


Monday, November 19, 2012 - 7:52:24 PM - Greg Robidoux Back To Top

@Brett - see answers below

1 - you could probably change this to VARCHAR if all of the data types are VARCHAR.  I think I did it this way to accomodate both VARCHAR and NVARCHAR.

2 - this just gets runs in the database you want to update you can just run this command

USE databaseName -- put in your database here
GO


Monday, November 19, 2012 - 1:35:12 PM - Brett Back To Top

Sorry but I am not so familiar with SQL but this is the function I need.  I am going to run this agains an existing database from a 3rd party vendor.  I just have two questions.

1.  I don't want to convert varchar to nvarchar as I am not sure of repercussions.  Can I just delete the convert statement as all the fields I wish to update are varchar.

2.  Where is the database name changed.

Thank you, Bret

 


Saturday, November 03, 2012 - 5:17:42 AM - kiran Back To Top

Cool srcipt. Helped lot to clear tables affected by sql injection.

:) :)


Tuesday, August 07, 2012 - 3:27:49 AM - Ashutosh Back To Top

its showing error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


Wednesday, August 01, 2012 - 11:20:12 AM - Kemre Back To Top

Great script and thanks for sharing!


Thursday, June 21, 2012 - 12:37:50 PM - Francesco Back To Top

Thanks a lot for this wonderful script!!!


Thursday, April 12, 2012 - 6:41:43 AM - Vikash Back To Top

many many thanks for this script.


Monday, March 19, 2012 - 9:40:20 PM - Scott Back To Top

I love you. You just saved me hours of work. An old, old script had an sql injection attack. What would have taken hours to clean up, is now good to go. Now I have time to go replace my old code with new stored procedures.

 

Cheers!


Saturday, October 30, 2010 - 6:30:21 AM - Sandeep Barua Back To Top

Thanks a lot of this wonder ful script SIR

You save my soul Wish u luck and keep posting more wonderful scripts like this

 

Thanks a lot once again


Saturday, August 14, 2010 - 2:44:12 AM - peters4oz Back To Top

 This script was a lifesaver.  Worked perfectly on a large Dot Net Nuke site that had to be migrated to another domain name where the content had many hardcoded links buried away in all sorts of tables.  Thanks a million!


Friday, July 17, 2009 - 4:51:09 AM - kfirake@gmail.com Back To Top

Hi All,

I got the above Stored Procedure which is really good. But if i want same procedure but with some small changes like,

"SQL Server Find and Replace Values in a specific Tables and All Text Columns"

then how do i code it.

 Thanks for all help.


Thursday, February 05, 2009 - 6:13:11 AM - grobido Back To Top

That is correct you can not use the Replace for Text and NText for SQL 2000.

SQL 2005 makes this a lot easier than SQL 2000.

 To update data in Text and NText columns in 2000 you need to use this approach.

Here is on example: http://sqlserver2000.databases.aspfaq.com/how-do-i-handle-replace-within-an-ntext-column-in-sql-server.html

 


Wednesday, February 04, 2009 - 11:19:18 PM - thinkerxp Back To Top

Hi, I didn't try your approach yet, 'cause i'm using sql server 2000.

But I know REPLACE function does not work with TEXT or NTEXT field. The ntext field can be replaced with your code?

 


Monday, October 06, 2008 - 11:14:36 AM - jwheat Back To Top

 Ahhh, yes, I imagine it is only SQL 2000 Server.

thanks for the quick reply.

 ... back to the drawing board


Monday, October 06, 2008 - 9:13:41 AM - aprato Back To Top

 The script is SQL Server 2005 specific.  Are you running it on a SQL 2000 server?


Monday, October 06, 2008 - 7:54:30 AM - jwheat Back To Top

 This just what I'm looking for, however when I run it I get the following -

Server: Msg 208, Level 16, State 1, Line 19
Invalid object name 'sys.objects'.
Server: Msg 208, Level 16, State 1, Line 19
Invalid object name 'sys.schemas'.

Any ideas?

  -Jon

 


Learn more about SQL Server tools