Search and Replace tool for MSSQL

Every now and then I come across some bad practices that needs repairing. This time the “no-no” was in a database tables. I had to change hard-coded links in quite a few tables. Replacing 10,000 absolute URLs spread across the database can be a huge headache, so I decided to work smart, not hard.

So I’ve started to work on a search & replace sproc using cursors (yes, I know…):

DECLARE @search varchar(1000)
SET @search = '-=search string=-'

DECLARE _cursor_ CURSOR LOCAL FAST_FORWARD
FOR
SELECT 
	ID,
	TEXTPTR(-=the filed=-),
	CHARINDEX(@search,-=the filed=- )-1
FROM -=the table=- 
WHRE -=the filed=- LIKE '%' + @search +'%'


DECLARE @replace varchar(1000)
SET @replace = '-=replace string=-'

DECLARE @length INT
SET @lenght = LEN(@replace)

DECLARE @ptr BINARY(16)
DECLARE @pos INT
DECLARE @id INT

OPEN _cursor_

FETCH NEXT FROM _cursor_ INTO @id, @ptr, @pos

WHILE @@fetch_status = 0
BEGIN	
 UPDATETEXT -=the table=-.-=the field=- @ptr @pos @txtlen @ntxt
 FETCH NEXT FROM _cursor_ INTO @id, @ptr, @pos	
END
CLOSE _cursor_
DEALLOCATE _cursor_

The initial query was working like a charm with the field data type being ntext; the only downside was if the string to replace was more than one time present in the field I had to run the query again and again. When I had to use it on another table with fields still on ntext, I’ve stumbled upon a strange error:

A cursor plan could not be generated for the given statement because
the textptr() function was used on a LOB column from one of the 
base tables.

The LOB column is a Large Object Column, so after a little research I wrote another query that fixed the issue and did not have the downside of the first one:

CREATE PROCEDURE FindReplace
(
@Table  varchar(20),
@Field  varchar(20),
@Where varchar(100),
@Find  varchar(30),
@Replace  varchar(30)
)
AS
DECLARE @query varchar(8000)
SET @query  = 'UPDATE ' +  @Table + 
              ' SET ' +  @Field + '= REPLACE(CONVERT(varchar(8000),' 
              + @Field + '),''' +  @Find + ''',''' + @Replace  +''')'
IF(@Where <> '')
	SET @query = @query + ' WHERE '+@Where
	
EXECUTE (@query)
GO
-- usage:
EXEC FindReplace 'table','field','where_clause','find','replace'

The only downside of this approach is that it doesn’t work on fields larger than 8000 chraracters.

I hope this will be helpful to someone. The code is open for improvement.