Replacing characters in a text field

I have a large table, tblMessage, which stores e-mail messages in text
fields. I need to remove the carriage returns the data in these fields,
but I have not yet figured out how to do so.

I thought that the way to do this would be with the REPLACE function;
unfortunately, of course, the REPLACE function cannot work with TEXT
fields. I tried CASTing the text field to VARCHAR(8000); however, some
of the rows have more than 8000 characters in the text field, so it bombs.

Here is the SQL that I tried:

select
msgID,
msgSent,
msgFromType,
msgFromID,
msgSubject,
REPLACE (CAST(msgMessage AS varchar(8000)), CHAR(13), '<BR>') AS
newMessage,
msgOriginal,
attID
into tblMessageNew
from tblMessage

I'm at my wit's end. Truncating the text field to 8000 character is an
acceptable option, but I can't even seem to be able to do that.

I'm using SQL Server version 7.Richard S. Crawford (rscrawfordDUCK@.mossREMOVEWATERFOWLroot.com) writes:
> I have a large table, tblMessage, which stores e-mail messages in text
> fields. I need to remove the carriage returns the data in these fields,
> but I have not yet figured out how to do so.
> I thought that the way to do this would be with the REPLACE function;
> unfortunately, of course, the REPLACE function cannot work with TEXT
> fields. I tried CASTing the text field to VARCHAR(8000); however, some
> of the rows have more than 8000 characters in the text field, so it bombs.

Bombs with what? It's always helpful if you include the error message.

I was able to run this on SQL Server 7:

create table hh (a text not null)
go
declare @.d varchar(8000), @.df varchar(8000)
select @.d = replicate('Why are you here? You should be there!', 8000/30)
select @.df = replicate('Why are you here? You should be there!', 8000/30)
insert hh (a)
exec ('select ''' + @.d + @.df + '''')
select datalength(a) from hh
select replace(cast(a as varchar(8000)), 'Why', 'Porque') from hh
go
drop table hh

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Taking the CR/LF out before inserting the text would avoid your problem!!!

The only way I can think of doing this is to chunk the text field into 8000
character as in
http://tinyurl.com/3fqxv

One way of doing this:
http://tinyurl.com/236hf

John

"Richard S. Crawford" <rscrawfordDUCK@.mossREMOVEWATERFOWLroot.com> wrote in
message news:ca4rog$d8b$1@.woodrow.ucdavis.edu...
> I have a large table, tblMessage, which stores e-mail messages in text
> fields. I need to remove the carriage returns the data in these fields,
> but I have not yet figured out how to do so.
> I thought that the way to do this would be with the REPLACE function;
> unfortunately, of course, the REPLACE function cannot work with TEXT
> fields. I tried CASTing the text field to VARCHAR(8000); however, some
> of the rows have more than 8000 characters in the text field, so it bombs.
> Here is the SQL that I tried:
> select
> msgID,
> msgSent,
> msgFromType,
> msgFromID,
> msgSubject,
> REPLACE (CAST(msgMessage AS varchar(8000)), CHAR(13), '<BR>') AS
> newMessage,
> msgOriginal,
> attID
> into tblMessageNew
> from tblMessage
> I'm at my wit's end. Truncating the text field to 8000 character is an
> acceptable option, but I can't even seem to be able to do that.
> I'm using SQL Server version 7.