Alex Rivera | Logout

t-sql replace on text field

Asked 2008-11-14T10:13:48.633
34

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.

Edit
Report

1 Answer

56

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>
answered 2009-10-26T08:52:18.383

Your Answer