Search and Replace substring of all nText fields

[esdaniel]

Does anyone have a script to replace a string within an nText field with another string for all nText fields in a SQL Svr 2K database?

Ed.

 

[Jamfool]

CREATE PROCEDURE SP_REPLACEALLTEXT (@TableName varchar(100), @FindText varchar(100), @ReplaceText varchar(100)) AS
DECLARE @SQL nvarchar(4000)
SET @SQL='UPDATE '+@TableName+' SET '
SELECT @FindText=REPLACE(@FindText, '''', ''''''), @ReplaceText=REPLACE(@ReplaceText, '''', '''''')
SELECT @SQL=@SQL+'['+COLUMN_NAME+']=REPLACE(['+COLUMN_NAME+'], '+''''+@FindText+''', '+''''+@ReplaceText+'''), ' FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar','char')
AND '['+TABLE_SCHEMA+'].['+TABLE_NAME+']'=@TableName

AND TABLE_NAME not like '%Sys_%'

IF @@ROWCOUNT > 0 BEGIN
SET @SQL=LEFT(@SQL, LEN(@SQL)-1)
PRINT(@SQL)
EXEC(@SQL)
PRINT @TableName+': done'
END
GO

EXEC SP_MSFOREACHTABLE 'EXEC SP_REPLACEALLTEXT ''?'', '''''''', ''`'''
DROP PROCEDURE SP_REPLACEALLTEXT


This will replace ' with ` in varchar and char columns. So you just need to change the data types, and the search and replace criteria. Also the AND TABLE_NAME not like '%Sys_%' part to exclude the tables u dont want to do.


regards

 

[esdaniel]

Sadly not - you can't use the REPLACE function with an nText column.

 

[gus121]

Hi there mate i found this on the microsoft site.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_md_04_6zec.asp

Please tell me how you get on and What solution you find to this problem. As i am trying to solve this issue my self.

thanks

Angus

Angus