Learn more about SQL Server tools

   
   















































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

MSSQLTips author Greg Robidoux By:   |   Read Comments (26)   |   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: 8/5/2008


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


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
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, July 29, 2014 - 1:58:52 PM - Kyle S Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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


Tuesday, January 08, 2013 - 5:47:22 PM - Nicholas Petersen Read The Tip

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 Read The Tip

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 Read The Tip

@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 Read The Tip

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 Read The Tip

@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 Read The Tip

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 Read The Tip

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

:) :)


Tuesday, August 07, 2012 - 3:27:49 AM - Ashutosh Read The Tip

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 Read The Tip

Great script and thanks for sharing!


Thursday, June 21, 2012 - 12:37:50 PM - Francesco Read The Tip

Thanks a lot for this wonderful script!!!


Thursday, April 12, 2012 - 6:41:43 AM - Vikash Read The Tip

many many thanks for this script.


Monday, March 19, 2012 - 9:40:20 PM - Scott Read The Tip

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 Read The Tip

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 Read The Tip

 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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

 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 Read The Tip

 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 Read The Tip

 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

 




 
Sponsor Information