Replace-type function for Text datatype

[Zack Sessions]

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]

Re: Replace-type function for Text datatype

Zack Sessions (zcsessions@vis ionair.com) writes:[color=blue]
> 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?[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[xAvailx]

Re: Replace-type function for Text datatype

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/default.asp?url=/library/en-us/ado270/htm/mdaexamples_vb01_8.asp

HTH,

BZ

zcsessions@visi onair.com (Zack Sessions) wrote in message news:<db13d9fb. 0308251131.3bb5 360d@posting.go ogle.com>...[color=blue]
> 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?[/color]

[Zack Sessions]

Re: Replace-type function for Text datatype

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns93E2DF B86DD18Yazorman @127.0.0.1>...[color=blue]
> Zack Sessions (zcsessions@vis ionair.com) writes:[color=green]
> > 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?[/color]
>
> 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.[/color]

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?