Update Text, NText Columns using UPDATETEXT Command in MS SQL Server
Text, NText field are common data types used as data types of table columns in MS SQL Server databases.
With Microsoft SQL Server 2005, new data types like varchar(max) and nvarchar(max) are introduced to SQL Server sql developers and SQL Server database administrators (DBA)
But probably in many SQL Server installations SQL200 databases are still active.
Or on places where SQL Server 2000 databases are migrated to or upgraded to SQL Server 2005, probably not yet those text and ntext columns are converted to new data types varchar(max) and nvarchar(max).
So we might need to work on text and ntext data type columns and deal with the limitations of text and ntext data types in t-sql.
Let's begin coding t-sql on your development or test SQL Server databases by creating a test table which we will insert some sample data.
After populating sql table with data sample data, we can execute UPDATETEXT command pver this sample SQL Server table in order to replace string values on data type TEXT and NTEXT columns.
CREATE TABLE TextData
(
id int identity(1,1),
txt ntext
)
Here is sample t-sql code using UPDATETEXT command.
DECLARE @id int
DECLARE @idTable TABLE (Id int)
INSERT INTO TextData
OUTPUT Inserted.Id INTO @idTable
SELECT
N'IĞüiŞçıÇÜşİöÖIĞüiŞçıÇÜşİöÖIĞüiŞçıÇÜşİöÖ'
SELECT @id = Id FROM @idTable
SELECT @id
DECLARE @ptrval binary(16);
DECLARE @insert_offset int;
SELECT @ptrval = TEXTPTR(txt) FROM textdata WHERE id = @id
SELECT
@insert_offset = PATINDEX(N'%çı%', txt) - 1
FROM textdata
WHERE id = @id
WHILE @insert_offset > -1
BEGIN
UPDATETEXT textdata.txt @ptrval @insert_offset 2 'XXXXXX';
SELECT
@insert_offset = PATINDEX(N'%çı%', txt) - 1
FROM textdata
WHERE id = @id
print @insert_offset
END
select * from textdata where Id = @Id
And I have build a sql stored procedure to execute dynamic t-sql codes on SQL Server databases for updating text or ntext column values.
CREATE PROCEDURE ReplaceTextColumn
(
@TableName sysname,
@ColumnName sysname,
@PKColumnName sysname,
@PKId bigint,
@OldString nvarchar(max),
@NewString nvarchar(max)
)
AS
SET NOCOUNT ON
DECLARE @SQL nvarchar(max)
SELECT @SQL = N'
DECLARE @ptrval binary(16);
DECLARE @insert_offset int;
SELECT
@ptrval = TEXTPTR('+ @ColumnName + ')
FROM ' + @TableName + '
WHERE
' + @PKColumnName + ' = ' + CAST(@PKId as varchar(10)) + '
SELECT
@insert_offset = PATINDEX(N''%' + @OldString + N'%'', '+ @ColumnName + ') - 1
FROM ' + @TableName + '
WHERE
' + @PKColumnName + ' = ' + CAST(@PKId as varchar(10)) + '
WHILE @insert_offset > -1
BEGIN
UPDATETEXT '+ @TableName + '.'+ @ColumnName + ' @ptrval @insert_offset ' + CAST(LEN(@OldString) as varchar(5)) + ' ''' + @NewString + ''';
SELECT
@insert_offset = PATINDEX(N''%' + @OldString + N'%'', '+ @ColumnName + ') - 1
FROM ' + @TableName + '
WHERE
' + @PKColumnName + ' = ' + CAST(@PKId as varchar(10)) + '
END
'
--print @SQL
EXEC sp_executesql @SQL
SET NOCOUNT OFF
GO
Here is an example showin the use the sql ReplaceTextColumn stored procedure in an.
EXEC ReplaceTextColumn N'TextData', N'txt', N'Id', 7, N'ĞüiŞçıÇÜş', N'T-SQL'
I hope this T-SQL sample codes using UPDATETEXT will help you to find and replace string value in text/ntext column.