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

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

On Mon, 14 Jul 2008 06:41:02 -0700, DXC
<DXC@discussions.microsoft.com> wrote:

>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

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



"Roy Harvey (SQL Server MVP)" wrote:

> 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
>
> On Mon, 14 Jul 2008 06:41:02 -0700, DXC
> <DXC@discussions.microsoft.com> wrote:
>
> >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
>

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.

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


"DXC" <DXC@discussions.microsoft.com> wrote in message
news:8DBA6441-4B6A-4D34-8828-EF518A28889A@microsoft.com...
> Thanks for the quick response Roy but Replace does not work on text
> fields.
>
>
>
> "Roy Harvey (SQL Server MVP)" wrote:
>
>> 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
>>
>> On Mon, 14 Jul 2008 06:41:02 -0700, DXC
>> <DXC@discussions.microsoft.com> wrote:
>>
>> >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
>>

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




"TheSQLGuru" wrote:

> 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.
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
>
> "DXC" <DXC@discussions.microsoft.com> wrote in message
> news:8DBA6441-4B6A-4D34-8828-EF518A28889A@microsoft.com...
> > Thanks for the quick response Roy but Replace does not work on text
> > fields.
> >
> >
> >
> > "Roy Harvey (SQL Server MVP)" wrote:
> >
> >> 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
> >>
> >> On Mon, 14 Jul 2008 06:41:02 -0700, DXC
> >> <DXC@discussions.microsoft.com> wrote:
> >>
> >> >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
> >>
>
>
>

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"
<DXC@discussions.microsoft.com> wrote:

> 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