t-sql replace on text field

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.

From stackoverflow

• 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

  Timothy Khouri : It's true :( .... that's why I love VARCHAR(MAX) in SQL Server 2005 which allows you to do all VARCHAR funtions on a 2gig length string :)

• 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
  

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

• 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)
  

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