MSSQL: Search and Replace in a TEXT(NTEXT) column
It's been a while since I've posted anything SQL related. So... Sometimes we need to search and replace a text value in the entire table. The column in question is of TEXT or NTEXT datatype. T-SQL REPLACE function does not work with TEXT/NTEXT datatype.
Instead we have to use several other functions:
- UPDATETEXT - Updates an existing text, ntext, or image field.
- TEXTPTR - Returns the text-pointer value that corresponds to a text, ntext, or image column in varbinary format. The retrieved text pointer value can be used in READTEXT, WRITETEXT, and UPDATETEXT statements.
- PATINDEX - Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.
A while ago I wrote a small utility procedure which uses above functions to implement Search and Replace functionality.
ALTER PROC dbo.SearchAndReplace
(
@FindString NVARCHAR(100)
,@ReplaceString NVARCHAR(100)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @TextPointer VARBINARY(16)
DECLARE @DeleteLength INT
DECLARE @OffSet INT
SELECT @TextPointer = TEXTPTR([MY_TEXT_COLUMN])
FROM [MY_TABLE]
SET @DeleteLength = LEN(@FindString)
SET @OffSet = 0
SET @FindString = '%' + @FindString + '%'
WHILE (SELECT COUNT(*)
FROM [MY_TABLE]
WHERE PATINDEX(@FindString, [MY_TEXT_COLUMN]) <> 0) > 0
BEGIN
SELECT @OffSet = PATINDEX(@FindString, [MY_TEXT_COLUMN]) - 1
FROM [MY_TABLE]
WHERE PATINDEX(@FindString, [MY_TEXT_COLUMN]) <> 0
UPDATETEXT [MY_TABLE].[MY_TEXT_COLUMN]
@TextPointer
@OffSet
@DeleteLength
@ReplaceString
END
SET NOCOUNT OFF
END
Hope this helps...
Saturday, May 3, 2008 8:16 PM
