Anatoly Lubarsky Logo
programming, design, integration, games, music

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


Related Posts:

Saturday, May 03, 2008 8:16 PM

Comments

#  Suchen und Ersetzen von Textteilen in MSSQL &raquo; Fractalcenter
Suchen und Ersetzen von Textteilen in MSSQL &raquo; Fractalcenter

3/28/2011 12:54 PM by Pingback/TrackBack

Login

Subscribe via RSS

Article Categories

.Net Framework
ASP.NET Tips
C# Win32 API
HTML, CSS, Web
Javascript Tips
MSSQL Tips
System
System.Net
WebServices

Archives

(02) January, 2018
(01) June, 2013
(03) March, 2013
(02) February, 2013
(01) July, 2012
(01) April, 2012
(01) September, 2011
(01) August, 2011
(03) May, 2011
(01) March, 2011
(02) December, 2010
(01) November, 2010
(01) October, 2010
(01) June, 2010
(01) May, 2010
(02) March, 2010
(01) January, 2010
(02) December, 2009
(03) September, 2009
(03) August, 2009
(09) July, 2009
(04) June, 2009
(03) May, 2009
(02) April, 2009
(03) March, 2009
(02) February, 2009
(02) January, 2009
(05) December, 2008
(04) November, 2008
(06) October, 2008
(04) September, 2008
(07) August, 2008
(04) July, 2008
(05) June, 2008
(07) May, 2008
(05) April, 2008
(03) March, 2008
(02) February, 2008
(03) January, 2008
(03) December, 2007
(05) November, 2007
(05) October, 2007
(07) September, 2007
(13) August, 2007
(11) July, 2007
(14) June, 2007
(16) May, 2007
(14) April, 2007
(10) March, 2007
(14) February, 2007
(20) January, 2007
(15) December, 2006
(12) November, 2006
(11) October, 2006
(13) September, 2006
(13) August, 2006
(12) July, 2006
(11) June, 2006
(12) May, 2006
(22) April, 2006
(30) March, 2006
(13) February, 2006
(14) January, 2006
(19) December, 2005
(17) November, 2005
(17) October, 2005
(17) September, 2005
(12) August, 2005
(14) July, 2005
(09) June, 2005
(15) May, 2005
(12) April, 2005
(21) March, 2005
(11) February, 2005
(12) January, 2005
(19) December, 2004
(13) November, 2004
(12) October, 2004
(15) September, 2004
(09) August, 2004
(25) July, 2004
(23) June, 2004
(31) May, 2004
(21) April, 2004
(16) March, 2004
(09) February, 2004
(06) January, 2004
(02) December, 2003
(01) November, 2003

Post Categories

.Net and C#
Android
Antispam
App. Development
Architecture
ASP.NET
Blogging
Deprecated Projects
Facebook Platform
Fun
Google
iOS
Javascript
Misc.
MSSQL
Music
My Games
Performance
Roller
Social Networks
Tools
Visual Studio
Web 2.0
WebServices

About Me

linkedin Profile
Recs
Who am I

My Sites

Billy Beet
x2line blogs