TSQL - Carriage control characters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DXC - 14 Jul 2008 14:41 GMT

SQL Server 2000 SP4.
I have this problem. I have a text column coming out in Some data Extract
for a client.
They need us to remove (replace) the carriage control characters in the
extract.
'--- vbCrLf & Chr(9)
I cannot convert to varchar because it will not fit.
What's my alternative.

Thanks for any help

Reply to this Message

Roy Harvey (SQL Server MVP) - 14 Jul 2008 15:13 GMT

declare @x varchar(30)
SET @x = 'banana' + char(13) + char(10) + 'split'
SELECT @x, REPLACE(@x, char(13) + char(10), '??')

------------------------------ --------------------------------------
banana
split banana??split

Roy Harvey
Beacon Falls, CT

>SQL Server 2000 SP4.
> I have this problem. I have a text column coming out in Some data Extract
[quoted text clipped - 6 lines]
>
>Thanks for any help

Reply to this Message

DXC - 14 Jul 2008 15:27 GMT

Thanks for the quick response Roy but Replace does not work on text fields.

> declare @x varchar(30)
> SET @x = 'banana' + char(13) + char(10) + 'split'
[quoted text clipped - 17 lines]
> >
> >Thanks for any help

Reply to this Message

TheSQLGuru - 14 Jul 2008 20:47 GMT

SUBSTRING does work on text IIRC, and inconjunction with patindex you should
be able to do what you need to do.

Also, if they are small text fields you can also first convert to varchar
and then process as Roy suggested. If they are large you will need to do
them in chunks of 8000 or so at a time.

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

> Thanks for the quick response Roy but Replace does not work on text
> fields.
[quoted text clipped - 21 lines]
>> >
>> >Thanks for any help

Reply to this Message

DXC - 15 Jul 2008 13:56 GMT

Thanks......................

> SUBSTRING does work on text IIRC, and inconjunction with patindex you should
> be able to do what you need to do.
[quoted text clipped - 28 lines]
> >> >
> >> >Thanks for any help

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 15 Jul 2008 17:48 GMT

What runs the extract? If you pull the data into a client language first
(VBScript, C#, VB.Net, etc) you can easily handle replace() before writing
to the file, and without having to deal with the limitations of TEXT.

On 7/14/08 9:41 AM, in article
7689CD4A-47FD-41B2-A5BB-495D86070AB0@microsoft.com, "DXC"

> SQL Server 2000 SP4.
> I have this problem. I have a text column coming out in Some data Extract
[quoted text clipped - 6 lines]
>
> Thanks for any help

Reply to this Message