Search and Replace tool for MSSQL

Share on facebook
Share on twitter
Share on linkedin
Share on reddit

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…):

  1. DECLARE @search varchar(1000)
  2. SET @search = '-=search string=-'
  3.  
  4. DECLARE _cursor_ CURSOR LOCAL FAST_FORWARD
  5. FOR
  6. SELECT 
  7. 	ID,
  8. 	TEXTPTR(-=the filed=-),
  9. 	CHARINDEX(@search,-=the filed=- )-1
  10. FROM -=the table=- 
  11. WHRE -=the filed=- LIKE '%' + @search +'%'
  12.  
  13.  
  14. DECLARE @replace varchar(1000)
  15. SET @replace = '-=replace string=-'
  16.  
  17. DECLARE @length INT
  18. SET @lenght = LEN(@replace)
  19.  
  20. DECLARE @ptr BINARY(16)
  21. DECLARE @pos INT
  22. DECLARE @id INT
  23.  
  24. OPEN _cursor_
  25.  
  26. FETCH NEXT FROM _cursor_ INTO @id, @ptr, @pos
  27.  
  28. WHILE @@fetch_status = 0
  29. BEGIN	
  30.  UPDATETEXT -=the table=-.-=the field=- @ptr @pos @txtlen @ntxt
  31.  FETCH NEXT FROM _cursor_ INTO @id, @ptr, @pos	
  32. END
  33. CLOSE _cursor_
  34. 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:

  1. CREATE PROCEDURE FindReplace
  2. (
  3. @Table  varchar(20),
  4. @Field  varchar(20),
  5. @Where varchar(100),
  6. @Find  varchar(30),
  7. @Replace  varchar(30)
  8. )
  9. AS
  10. DECLARE @query varchar(8000)
  11. SET @query  = 'UPDATE ' +  @Table + 
  12.               ' SET ' +  @Field + '= REPLACE(CONVERT(varchar(8000),' 
  13.               + @Field + '),''' +  @Find + ''',''' + @Replace  +''')'
  14. IF(@Where <> '')
  15. 	SET @query = @query + ' WHERE '+@Where
  16.  
  17. EXECUTE (@query)
  18. GO
  19. -- usage:
  20. 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.

Armand Niculescu

Armand Niculescu

As the Senior Project manager, Armand is one of the rare kind of developers that can do both design and programming with equal skill. This, coupled with a solid background and many years of experience, enables him to see the big picture and plan for the small details.

7 Responses

  1. Awesome script. It took me some time to find something like this. I tried updating it through ColdFusion and it worked, but it wasn’t very flexible. Know to little about sql scripting to give you any suggestions, sorry for that.

    Cheers,
    JP

  2. Was just curious if there was a way to check a WHOLE database instead of just one table for this script. Im not SQL oriented enough to edit the script myself so..

Comments are closed.