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

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)

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.

like so:

BEGIN TRANSACTION; 
UPDATE table_name
  SET column_name=REPLACE(column_name,'text_to_find','replace_with_this'); 
COMMIT TRANSACTION;

Example: Replaces <script... with <a ... to eliminate javascript vulnerabilities

BEGIN TRANSACTION; UPDATE testdb
SET title=REPLACE(title,'script','a'); COMMIT TRANSACTION;

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!

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) > ''' + @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) > ''' + @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

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.