How do I do a simple Find and Replace in MsSQL

Question

Question is pretty self explanitory  I want to do a simple find and replace  like you would in a text editor on the data in a column of my database  which is MsSQL on MS Windows server 2003

User · Answer

like so   BEGIN TRANSACTION   UPDATE table name   SET column name REPLACE column name  text to find   replace with this     COMMIT TRANSACTION    Example  Replaces  lt script    with  lt a     to eliminate javascript vulnerabilities  BEGIN TRANSACTION  UPDATE testdb SET title REPLACE title  script   a    COMMIT TRANSACTION

User · Answer

The following will find and replace a string in every database  excluding system databases  on every table on the instance you are connected to   Simply change  Search String  to whatever you seek and  Replace String  with whatever you want to replace it with   --Getting all the databases and making a cursor DECLARE db cursor CURSOR FOR   SELECT name  FROM master dbo sysdatabases  WHERE name NOT IN   master   model   msdb   tempdb    -- exclude these databases  DECLARE  databaseName nvarchar 1000  --opening the cursor to move over the databases in this instance OPEN db cursor FETCH NEXT FROM db cursor INTO  databaseName     WHILE   FETCH STATUS   0    BEGIN     PRINT  databaseName     --Setting up temp table for the results of our search     DECLARE  Results TABLE TableName nvarchar 370   RealColumnName nvarchar 370   ColumnName nvarchar 370   ColumnValue nvarchar 3630        SET NOCOUNT ON      DECLARE  SearchStr nvarchar 100    ReplaceStr nvarchar 100    SearchStr2 nvarchar 110      SET  SearchStr    Search String      SET  ReplaceStr    Replace String      SET  SearchStr2   QUOTENAME        SearchStr                  DECLARE  TableName nvarchar 256    ColumnName nvarchar 128      SET   TableName           --Looping over all the tables in the database     WHILE  TableName IS NOT NULL     BEGIN         DECLARE  SQL nvarchar 2000          SET  ColumnName              DECLARE  result NVARCHAR 256          SET  SQL    USE      databaseName                 SELECT  result   MIN QUOTENAME TABLE SCHEMA            QUOTENAME TABLE NAME               FROM          databaseName      INFORMATION SCHEMA TABLES             WHERE       TABLE TYPE     BASE TABLE   AND TABLE CATALOG          databaseName                       AND QUOTENAME TABLE SCHEMA            QUOTENAME TABLE NAME   gt         TableName                       AND OBJECTPROPERTY                          OBJECT ID                              QUOTENAME TABLE SCHEMA            QUOTENAME TABLE NAME                                       IsMSShipped                                       0          EXEC master  sp executesql  SQL  N  result nvarchar 256  out    result out          SET  TableName    result         PRINT  TableName          WHILE   TableName IS NOT NULL  AND   ColumnName IS NOT NULL          BEGIN             DECLARE  ColumnResult NVARCHAR 256              SET  SQL                     SELECT  ColumnResult   MIN QUOTENAME COLUMN NAME                   FROM          databaseName      INFORMATION SCHEMA COLUMNS                 WHERE       TABLE SCHEMA      PARSENAME         databaseName           TableName        2                      AND TABLE NAME    PARSENAME         databaseName           TableName        1                      AND DATA TYPE IN    char      varchar      nchar      nvarchar                        AND TABLE CATALOG          databaseName                           AND QUOTENAME COLUMN NAME   gt         ColumnName                    PRINT  SQL             EXEC master  sp executesql  SQL  N  ColumnResult nvarchar 256  out    ColumnResult out             SET  ColumnName    ColumnResult               PRINT  ColumnName              IF  ColumnName IS NOT NULL             BEGIN                 INSERT INTO  Results                 EXEC                                        USE      databaseName                         SELECT        TableName              ColumnName              TableName          ColumnName        LEFT      ColumnName      3630                       FROM      TableName      NOLOCK                            WHERE      ColumnName     LIKE      SearchStr2                               END         END     END      --Declaring another temporary table     DECLARE  time to update TABLE TableName nvarchar 370   RealColumnName nvarchar 370        INSERT INTO  time to update     SELECT TableName  RealColumnName FROM  Results GROUP BY TableName  RealColumnName      DECLARE  MyCursor CURSOR      BEGIN         DECLARE  t nvarchar 370          DECLARE  c nvarchar 370          --Looping over the search results            SET  MyCursor   CURSOR FOR         SELECT TableName  RealColumnName FROM  time to update GROUP BY TableName  RealColumnName          --Getting my variables from the first item         OPEN  MyCursor          FETCH NEXT FROM  MyCursor          INTO  t   c          WHILE   FETCH STATUS   0         BEGIN             -- Updating the old values with the new value             DECLARE  sqlCommand varchar 1000              SET  sqlCommand                     USE      databaseName                     UPDATE       databaseName           t     SET      c       REPLACE      c             SearchStr               ReplaceStr                         WHERE      c     LIKE        SearchStr2                    PRINT  sqlCommand             BEGIN TRY                 EXEC   sqlCommand              END TRY             BEGIN CATCH                 PRINT ERROR MESSAGE               END CATCH              --Getting next row values             FETCH NEXT FROM  MyCursor              INTO  t   c          END           CLOSE  MyCursor           DEALLOCATE  MyCursor      END       DELETE FROM  time to update     DELETE FROM  Results      FETCH NEXT FROM db cursor INTO  databaseName END     CLOSE db cursor    DEALLOCATE db cursor   Note  this isn t ideal  nor is it optimized

User · Answer

If you are working with SQL Server 2005 or later there is also a CLR library available at http   www sqlsharp com  that provides  NET implementations of string and RegEx functions which  depending on your volume and type of data may be easier to use and in some cases the  NET string manipulation functions can be more efficient than T-SQL ones

User · Answer

The following query replace each and every a character with a b character   UPDATE      YourTable SET      Column1   REPLACE Column1  a   b   WHERE      Column1 LIKE   a     This will not work on SQL server 2003

User · Answer

This pointed me in the right direction  but I have a DB that originated in MSSQL 2000 and is still using the ntext data type for the column I was replacing on  When you try to run REPLACE on that type you get this error      Argument data type ntext is invalid for argument 1 of replace   function    The simplest fix  if your column data fits within nvarchar  is to cast the column during replace  Borrowing the code from the accepted answer   UPDATE YourTable SET Column1   REPLACE cast Column1 as nvarchar max    a   b   WHERE Column1 LIKE   a     This worked perfectly for me  Thanks to this forum post I found for the fix  Hopefully this helps someone else

[sql] How do I do a simple 'Find and Replace" in MsSQL?

Examples related to sql

Examples related to sql-server