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

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> 

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