can anyone show me an example of using SQL server UPDATETEXT funciton.I want to app开发者_运维问答ly some text replacement from one column(which is of type text) from one of my table
From Books Online:
USE pubs;
GO
ALTER DATABASE pubs SET RECOVERY SIMPLE;
GO
DECLARE @ptrval binary(16);
SELECT @ptrval = TEXTPTR(pr_info)
FROM pub_info pr, publishers p
WHERE p.pub_id = pr.pub_id
AND p.pub_name = 'New Moon Books'
UPDATETEXT pub_info.pr_info @ptrval 88 1 'b';
GO
ALTER DATABASE pubs SET RECOVERY FULL;
GO
More info here.
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'
For more information you can read from here Example and SP
Hope this helps!
精彩评论