<p>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)</p>

[{'answer_id': 59055, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 8, 'selected': True, 'text': "<p>The following query replace each and every <code>a</code> character with a <code>b</code> character.</p>\n\n<pre><code>UPDATE \n    YourTable\nSET \n    Column1 = REPLACE(Column1,'a','b')\nWHERE \n    Column1 LIKE '%a%'\n</code></pre>\n\n<p>This will not work on SQL server 2003.</p>\n"}, {'answer_id': 59057, 'author': 'Jiaaro', 'author_id': 2908, 'author_profile': 'https://Stackoverflow.com/users/2908', 'pm_score': 4, 'selected': False, 'text': "<p>like so:</p>\n\n<pre><code>BEGIN TRANSACTION; \nUPDATE table_name\n  SET column_name=REPLACE(column_name,'text_to_find','replace_with_this'); \nCOMMIT TRANSACTION;\n</code></pre>\n\n<p>Example: Replaces &lt;script... with &lt;a ... to eliminate javascript vulnerabilities</p>\n\n<pre><code>BEGIN TRANSACTION; UPDATE testdb\nSET title=REPLACE(title,'script','a'); COMMIT TRANSACTION;\n</code></pre>\n"}, {'answer_id': 59474, 'author': 'Joe Kuemerle', 'author_id': 4273, 'author_profile': 'https://Stackoverflow.com/users/4273', 'pm_score': 0, 'selected': False, 'text': '<p>If you are working with SQL Server 2005 or later there is also a CLR library available at <a href="http://www.sqlsharp.com/" rel="nofollow noreferrer">http://www.sqlsharp.com/</a> 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.</p>\n'}, {'answer_id': 9707239, 'author': 'Brian Moeskau', 'author_id': 108348, 'author_profile': 'https://Stackoverflow.com/users/108348', 'pm_score': 3, 'selected': False, 'text': '<p>This pointed me in the right direction, but I have a DB that originated in MSSQL 2000 and is still using the <code>ntext</code> data type for the column I was replacing on. When you try to run REPLACE on that type you get this error:</p>\n\n<blockquote>\n  <p>Argument data type ntext is invalid for argument 1 of replace\n  function.</p>\n</blockquote>\n\n<p>The simplest fix, if your column data fits within <code>nvarchar</code>, is to cast the column during replace. Borrowing the code from the <a href="https://stackoverflow.com/a/59055/108348">accepted answer</a>:</p>\n\n<pre><code>UPDATE YourTable\nSET Column1 = REPLACE(cast(Column1 as nvarchar(max)),\'a\',\'b\')\nWHERE Column1 LIKE \'%a%\'\n</code></pre>\n\n<p>This worked perfectly for me. Thanks to <a href="http://www.sqlservercentral.com/Forums/FindPost316385.aspx" rel="nofollow noreferrer">this forum post</a> I found for the fix. Hopefully this helps someone else!</p>\n'}, {'answer_id': 31589027, 'author': 'abc123', 'author_id': 1985032, 'author_profile': 'https://Stackoverflow.com/users/1985032', 'pm_score': 2, 'selected': False, 'text': "<h2>The following will find and replace a string in every database (excluding system databases) on every table on the instance you are connected to:</h2>\n\n<p>Simply change <code>'Search String'</code> to whatever you seek and <code>'Replace String'</code> with whatever you want to replace it with.</p>\n\n<pre><code>--Getting all the databases and making a cursor\nDECLARE db_cursor CURSOR FOR  \nSELECT name \nFROM master.dbo.sysdatabases \nWHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases\n\nDECLARE @databaseName nvarchar(1000)\n--opening the cursor to move over the databases in this instance\nOPEN db_cursor\nFETCH NEXT FROM db_cursor INTO @databaseName   \n\nWHILE @@FETCH_STATUS = 0   \nBEGIN\n    PRINT @databaseName\n    --Setting up temp table for the results of our search\n    DECLARE @Results TABLE(TableName nvarchar(370), RealColumnName nvarchar(370), ColumnName nvarchar(370), ColumnValue nvarchar(3630))\n\n    SET NOCOUNT ON\n\n    DECLARE @SearchStr nvarchar(100), @ReplaceStr nvarchar(100), @SearchStr2 nvarchar(110)\n    SET @SearchStr = 'Search String'\n    SET @ReplaceStr = 'Replace String'\n    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')\n\n    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128)\n    SET  @TableName = ''\n\n    --Looping over all the tables in the database\n    WHILE @TableName IS NOT NULL\n    BEGIN\n        DECLARE @SQL nvarchar(2000)\n        SET @ColumnName = ''\n        DECLARE @result NVARCHAR(256)\n        SET @SQL = 'USE ' + @databaseName + '\n            SELECT @result = MIN(QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME))\n            FROM    [' + @databaseName + '].INFORMATION_SCHEMA.TABLES\n            WHERE       TABLE_TYPE = ''BASE TABLE'' AND TABLE_CATALOG = ''' + @databaseName + '''\n                AND QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME) &gt; ''' + @TableName + '''\n                AND OBJECTPROPERTY(\n                        OBJECT_ID(\n                            QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME)\n                                ), ''IsMSShipped''\n                                ) = 0'\n        EXEC master..sp_executesql @SQL, N'@result nvarchar(256) out', @result out\n\n        SET @TableName = @result\n        PRINT @TableName\n\n        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)\n        BEGIN\n            DECLARE @ColumnResult NVARCHAR(256)\n            SET @SQL = '\n                SELECT @ColumnResult = MIN(QUOTENAME(COLUMN_NAME))\n                FROM    [' + @databaseName + '].INFORMATION_SCHEMA.COLUMNS\n                WHERE       TABLE_SCHEMA    = PARSENAME(''[' + @databaseName + '].' + @TableName + ''', 2)\n                    AND TABLE_NAME  = PARSENAME(''[' + @databaseName + '].' + @TableName + ''', 1)\n                    AND DATA_TYPE IN (''char'', ''varchar'', ''nchar'', ''nvarchar'')\n                    AND TABLE_CATALOG = ''' + @databaseName + '''\n                    AND QUOTENAME(COLUMN_NAME) &gt; ''' + @ColumnName + ''''\n            PRINT @SQL\n            EXEC master..sp_executesql @SQL, N'@ColumnResult nvarchar(256) out', @ColumnResult out\n            SET @ColumnName = @ColumnResult \n\n            PRINT @ColumnName\n\n            IF @ColumnName IS NOT NULL\n            BEGIN\n                INSERT INTO @Results\n                EXEC\n                (\n                    'USE ' + @databaseName + '\n                    SELECT ''' + @TableName + ''',''' + @ColumnName + ''',''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) \n                    FROM ' + @TableName + ' (NOLOCK) ' +\n                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2\n                )\n            END\n        END\n    END\n\n    --Declaring another temporary table\n    DECLARE @time_to_update TABLE(TableName nvarchar(370), RealColumnName nvarchar(370))\n\n    INSERT INTO @time_to_update\n    SELECT TableName, RealColumnName FROM @Results GROUP BY TableName, RealColumnName\n\n    DECLARE @MyCursor CURSOR;\n    BEGIN\n        DECLARE @t nvarchar(370)\n        DECLARE @c nvarchar(370)\n        --Looping over the search results   \n        SET @MyCursor = CURSOR FOR\n        SELECT TableName, RealColumnName FROM @time_to_update GROUP BY TableName, RealColumnName\n\n        --Getting my variables from the first item\n        OPEN @MyCursor \n        FETCH NEXT FROM @MyCursor \n        INTO @t, @c\n\n        WHILE @@FETCH_STATUS = 0\n        BEGIN\n            -- Updating the old values with the new value\n            DECLARE @sqlCommand varchar(1000)\n            SET @sqlCommand = '\n                USE ' + @databaseName + '\n                UPDATE [' + @databaseName + '].' + @t + ' SET ' + @c + ' = REPLACE(' + @c + ', ''' + @SearchStr + ''', ''' + @ReplaceStr + ''') \n                WHERE ' + @c + ' LIKE ''' + @SearchStr2 + ''''\n            PRINT @sqlCommand\n            BEGIN TRY\n                EXEC (@sqlCommand)\n            END TRY\n            BEGIN CATCH\n                PRINT ERROR_MESSAGE()\n            END CATCH\n\n            --Getting next row values\n            FETCH NEXT FROM @MyCursor \n            INTO @t, @c \n        END;\n\n        CLOSE @MyCursor ;\n        DEALLOCATE @MyCursor;\n    END;\n\n    DELETE FROM @time_to_update\n    DELETE FROM @Results\n\n    FETCH NEXT FROM db_cursor INTO @databaseName\nEND   \n\nCLOSE db_cursor   \nDEALLOCATE db_cursor\n</code></pre>\n\n<p><strong><em>Note: this isn't ideal, nor is it optimized</em></strong></p>\n"}]
['https://Stackoverflow.com/questions/59044', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2908/']