Replacing Carriage Returns

[Eyespi20]

I have several files that come to me from outside sources which, in text fields often contain carriage returns. My processes handle these ok, but my problem lies in spitting it back out in a tab delimited file for reporting. The carriage returns are causing problems there.

Can you suggest a way to strip those out of the results?

Thanks in advance for your help.

Margaret

 

[donutman]

Why are you asking for a SQL solution or did you post in the wrong forum? You aren't saying that the column data includes a CRLF sequence are you?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Eyespi20]

Because I'm pulling the files into a SQL database, querying my data against it and spitting out the resultset.

Also, some of the data in my database has carriage returns in it which also need to be removed to produce a solid working file.

Thanks!

Margaret

 

[donutman]

For existing column data I would use an Update statement that uses the Replace command. For text files I would use a VB program or word processor, whichever is more appropriate.
The Replace command works like this:
Replace(Str1, Str2, Str3)
Str1 is the CRLF string and/or just CR
Str2 is the Column data
Str3 is the string to replace Str1, i.e. ''
Does that help?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Eyespi20]

If I understand you correctly the following should work:

declare @cr varchar(5)
set @cr = '
'

replace(text, @cr, '')

go

 

[donutman]

This is more like it:

[Blue]DECLARE[/Blue] @CR [Blue]AS[/Blue] [Fuchsia]Char[/Fuchsia][Gray]([/Gray]2[Gray])[/Gray]
[Blue]SET[/Blue] @CR[Gray]=[/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]13[Gray])[/Gray][Gray]+[/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]10[Gray])[/Gray]
[Fuchsia]Replace[/Fuchsia] [Gray]([/Gray]@CR[Gray],[/Gray] [Blue]text[/Blue][Gray],[/Gray][red]''[/red][Gray])[/Gray]

But it's also possible that you need to do a 2nd replace looking only for char(13).
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Eyespi20]

Query analyzer gives me incorrect syntax error at replace when I have it that way.

 

[dbomrrsm]

declare @cr varchar(5)
set @cr = ''
select replace('text', @cr, '')

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

this PLACES a carriage return in a string:

DECLARE @CRLF CHAR(2)
SET @CRLF = CHAR(13) + CHAR(10)
PRINT 'line 1' + @CRLF + 'line 2'
[code]

[bandito] [blue]DBomrrsm[/blue] [bandito]

 

[Eyespi20]

Some questions, Dbomrrsm

Why would I use single quotes around my column name?

How is setting @cr = ' ' not setting it to a space rather than a carriage return?

I still get a syntax error at replace

Thanks

Margaret

 

[donutman]

Sorry, I had the defination of Str1 and Str2 reversed.

[Blue]DECLARE[/Blue] @CR [Blue]AS[/Blue] [Fuchsia]Char[/Fuchsia][Gray]([/Gray]2[Gray])[/Gray]
[Blue]SET[/Blue] @CR[Gray]=[/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]13[Gray])[/Gray][Gray]+[/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]10[Gray])[/Gray]
[Fuchsia]Replace[/Fuchsia] [Gray]([/Gray]YourTextField[Gray],[/Gray]@CR[Gray],[/Gray][red]''[/red][Gray])[/Gray]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[dbomrrsm]

Margaret

'test' isnt a column name its just some text and as such needs '' around it.

What donutman has just posted should do it for you - setting @CR = Char(13)+Char(10) sets @CR as a carriage return. then using replace the last line replcaes any carriage returns in YourTextField with '' - which is nothing !!

Well done donutman.

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

Margaret

BTW '' without a space between then is nothing - I must agree they do look like they are set to have one space between them but they haven't.

[blue]DBomrrsm[/blue]

 

[Eyespi20]

Thanks.

I really appreciate everyone's help!

Margaret