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…):
T-SQL:
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:
T-SQL:
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.
Permalink1 comment
Cheers,
JP
Comment feed for this postLeave a comment
Aggregated by MXNA
