Replace on a text field.

I need to search for all occurances of particular string within a column on a table. The column has a data type of Text. It will not allow me to use the replace function on a Text field only on varchars or chars. Does anybody have any ideas of how I can do this?njjones,

Look at Full Text Indexing in Books Online (BOL).

If however you can guarantee that none of the fields exceed 8000 characters you could CAST the TEXT field to a VARCHAR(8000). ie.

REPLACE(CAST(yourtextcolumn AS VARCHAR(8000)),'ABC','DEF')

macka.|||I have done that however quite a lot of the fields I need to affect are greater than 8000 characters (hence using the text datatype). I wanted to run a query to find out how many were longer but you can't use Len on a text field either - is there an easy way of finding the character length of text in a text column?|||Chances are not many will be exactly 8000 in length, so the following query gives you a rough idea of how many are bigger than 8k, but truncating all fields to 8000 characters.

SELECT COUNT(*)
FROM yourtable
WHERE LEN((CAST(yourtextcolumn AS VARCHAR(8000)))) = 8000

macka.|||The following works - seems a little heavy handed for a replacement of a one line function, but any:

declare datacursor cursor
for
select
dataid, TEXTPTR(description)
from
tbl_data
where
description like '%25.224.8.30%'
declare @.ptrval binary(16)
declare @.dataid int
declare @.pos1 int
open datacursor
fetch next from datacursor
into @.dataid, @.ptrval
while @.@.fetch_status = 0
begin
select @.pos1 = patindex('%25.224.8.30%',tbl_data.description) from
tbl_data where dataid = @.dataid
while @.pos1 <> 0
begin
set @.pos1 = @.pos1-1
updatetext tbl_data.description @.ptrval @.pos1 11
'modconnect1.qinetiq.r.mil.uk'
select @.pos1 =
patindex('%25.224.8.30%',tbl_data.description) from tbl_data where dataid =
@.dataid
end
fetch next from datacursor
into @.dataid, @.ptrval
end
close datacursor
deallocate datacursor|||njjones,

Did you ever get the 'replace' issue resolved in a Text field? I need to do a similar action, finding all the commas in a text field and replacing it with a semi-colon.

Thanks.|||The answer is above, however I have recopied and pasted it below and updated it so that it should work for , and ; - probably could have parameterised this and turned it in a user defined function but it is not something I have needed to do often enough to bother with:

declare datacursor cursor
for
select
dataid, TEXTPTR(description)
from
tbl_data
where
description like '%,%'
declare @.ptrval binary(16)
declare @.dataid int
declare @.pos1 int
open datacursor
fetch next from datacursor
into @.dataid, @.ptrval
while @.@.fetch_status = 0
begin
select @.pos1 = patindex('%,%',tbl_data.description) from
tbl_data where dataid = @.dataid
while @.pos1 <> 0
begin
set @.pos1 = @.pos1-1
updatetext tbl_data.description @.ptrval @.pos1 1
';'
select @.pos1 =
patindex('%,%',tbl_data.description) from tbl_data where dataid =
@.dataid
end
fetch next from datacursor
into @.dataid, @.ptrval
end
close datacursor
deallocate datacursor|||Nicky, thanks. A couple of quick modifications and I had this working well for my table. I appreciate it.

RY