Replacing characters in a text field

[Richard S. Crawford]

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.

[Erland Sommarskog]

Richard S. Crawford (rs************@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, es****@sommarskog.se

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

[John Bell]

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" <rs************@mossREMOVEWATERFOWLroot.com> wrote in
message news:ca**********@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.