CREATE PROCEDURE dbo.usp_utility_ReplaceText
@TableName VARCHAR(32),
@ColumnName VARCHAR(32),
@otxt VARCHAR(100),
@ntxt VARCHAR(100)
AS
DECLARE @num INT
DECLARE @sql NVARCHAR(4000)
DECLARE @parms NVARCHAR(4000)
DECLARE @invalidChars VARCHAR(20)
SET @invalidChars = '%[^a-z1-9_@#$]%'
IF PATINDEX(@invalidChars, @TableName) > 0
BEGIN
PRINT 'Invalid character in table name.'
RETURN
END
IF PATINDEX(@invalidChars, @ColumnName) > 0
BEGIN
PRINT 'Invalid character in column name.'
RETURN
END
IF CHARINDEX(@ntxt, @otxt) > 0
BEGIN
PRINT 'Sorry, the new text cannot contain the old text.'
RETURN
END
SET @sql = N'
DECLARE @txtlen INT
DECLARE @ptr BINARY(16)
DECLARE @pos INT
DECLARE @otxtPat VARCHAR(100)
DECLARE @more bit
SET @txtlen = LEN(@otxtIN)
SET @otxtPat = @otxtIN
SET @otxtPat = REPLACE(@otxtPat,''%'',''[%]'')
SET @otxtPat = REPLACE(@otxtPat,''_'',''[_]'')
SET @otxtPat = ''%'' + @otxtPat + ''%''
SET @numOUT = 0
WHILE EXISTS (
SELECT * FROM ' + @TableName + '
WHERE ' + @ColumnName + ' LIKE @otxtPat
)
BEGIN
DECLARE curs CURSOR local fast_forward
FOR SELECT
TEXTPTR(' + @ColumnName + '),
PATINDEX(@otxtPat,
' + @ColumnName + ') - 1
FROM ' + @TableName + '
WHERE ' + @ColumnName + ' LIKE @otxtPat
OPEN curs
FETCH NEXT FROM curs INTO @ptr, @pos
WHILE @@fetch_status = 0
BEGIN
UPDATETEXT
' + @TableName + '.' + @ColumnName + '
@ptr @pos @txtlen @ntxtIN
SET @numOUT = @numOUT + 1
FETCH NEXT FROM curs INTO @ptr, @pos
END
CLOSE curs
DEALLOCATE curs
END'
SET @parms = N'@otxtIN VARCHAR(100),@ntxtIN VARCHAR(100),@numOUT INT OUTPUT'
EXEC sp_executesql @sql, @parms,
@otxtIN = @otxt,
@ntxtIN = @ntxt,
@numOUT = @num OUTPUT
PRINT CONVERT(VARCHAR(32), @num) + ' occurrences replaced.'
GO
