Replace-type function for Text datatype

Zack Sessions (zc********@visionair.com) writes:

I have a table that has a Text datatype column that has gotten some
garbage
characters in it somehow, probably from key entry. I need to remove
the garbage, multiple occurances of char(15). The replace function
does not work on Text datatype. Any suggestions?

One way would be to iterate over the table, and for each row get slices
of 8000 chars to a varchar value on which you run replace(). You would
then use updatetext to update the row. A bit tricky, because if first
got chars 1 to 8000, and removed 6 char(15), you should now start on
char 7994 for the next batch.

Not particularly funny, I know.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Hello:

You could write a small vbscript that would loop thru the table and
update the text columns using ado's appendchunk method and the replace
function in vbscript.

See link below on an example that you can adapt to vbscript and your
problem:

http://msdn.microsoft.com/library/de...les_vb01_8.asp

HTH,

BZ

zc********@visionair.com (Zack Sessions) wrote in message news:<db**************************@posting.google. com>...

I have a table that has a Text datatype column that has gotten some
garbage
characters in it somehow, probably from key entry. I need to remove
the garbage, multiple occurances of char(15). The replace function
does not work on Text datatype. Any suggestions?

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...

Zack Sessions (zc********@visionair.com) writes:

I have a table that has a Text datatype column that has gotten some
garbage
characters in it somehow, probably from key entry. I need to remove
the garbage, multiple occurances of char(15). The replace function
does not work on Text datatype. Any suggestions?

One way would be to iterate over the table, and for each row get slices
of 8000 chars to a varchar value on which you run replace(). You would
then use updatetext to update the row. A bit tricky, because if first
got chars 1 to 8000, and removed 6 char(15), you should now start on
char 7994 for the next batch.

Not particularly funny, I know.

Thanks for your response.

I actually thought of trying to do it this way and started to write
the code, but I got stuck on how to get the 8000 character chunks. The
way I read the READTEXT description, it does not return the value into
a local variable. I know how to get the first 8000 characters into a
local varchar, but I haven't figured out how to get any remaining 8000
character chunks. Care to give me a little more help?