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.
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.