SQL: UpdateText function: how to use?

Question

can someone show me an example of using SQL server UPDATETEXT funciton.I want to apply some text replacement from one column (which has a text type) from one of my tables

Answer 1

From books on the Internet:

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 details here .

Answer 2

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 showing using ReplaceTextColumn's sql stored procedure.

EXEC ReplaceTextColumn N'TextData', N'txt', N'Id', 7, N'ĞüiŞçıÇÜş', N'T-SQL'

You can find more information here Example and SP

Hope this helps!