t-sql replace on text field

Question

I have hit a classic problem of needing to do a string replace on a text field in an sql 2000 database. This could either be an update over a whole column or a single field I'm not fussy.

I have found a few examples of how to use updatetext to achieve it but they tend to be in stored procedures, does anyone know of a similar thing that is wrapped into a function so I can use it like I would usually use Replace(). The problem with the Replace() function for anyone who isn't aware is that it doesn't support text fields.

Edit: I realised I could probably get away with varchar(8000) so have swapped the fields to this type which fixes the issue. I never found a true solution.

Answer 1

This is my code snippet for this scenario:

DECLARE @oldtext    varchar(1000)
DECLARE @newtext    varchar(1000)
DECLARE @textlen    int
DECLARE @ptr        binary(16)
DECLARE @pos        int
DECLARE @id         uniqueidentifier

SET @oldtext = 'oldtext'
SET @newtext = 'newtext'
SET @textlen = LEN(@oldtext)

DECLARE mycursor CURSOR LOCAL FAST_FORWARD
FOR
    SELECT  [UniqueID]
            ,TEXTPTR([Text])
            ,CHARINDEX(@oldtext, [Text]) - 1
    FROM    [dbo].[myTable] 
    WHERE   [Text] LIKE '%' + @oldtext +'%'

OPEN mycursor

FETCH NEXT FROM mycursor into @id, @ptr, @pos

WHILE @@fetch_status = 0
BEGIN   
    UPDATETEXT [dbo].[myTable].Text @ptr @pos @textlen @newtext

    FETCH NEXT FROM mycursor into @id, @ptr, @pos   
END

CLOSE mycursor
DEALLOCATE mycursor

Answer 2

I am afraid you cannot do it within a function

When you try to declare a function like:

create function dbo.textReplace(
@inText as text)
returns text
as 
begin
    return 'a' -- just dummy code
end

You will get the following error:

The text data type is invalid for return values.

In other words you could not write a simple equivalent of REPLACE function for the text data type

Answer 3

You would have to cast the text field to a varchar(8000) or nvarchar(4000) if you are replacing over an ntext field.

MyField = REPLACE(CAST(MyField as VARCHAR(4000)), "string1", "string2")

This ofcourse will only work if you can guarantee the content in the field is <= 4000/8000 characters in length.

Answer 4

You can also use the SUBSTRING() function, which returns a varchar when passed a text value.

For instance:

MyVarchar = SUBSTRING(myTextField, 1, DATALENGTH(myTextField))

If you're populating a varchar with a specific length, you can truncate to fit:

MyVarchar100 = SUBSTRING(myTextField, 1, 100)

Answer 5

Here is the sample query to update table with text column using REPLACE function. Hope this is useful for you.

UPDATE <Table> set textcolumn=
REPLACE(SUBSTRING(textcolumn,1,DATALENGTH(textcolumn)),'findtext','replacetext') 
WHERE <Condition>