removing chr(13) and chr(10) from SQL

ics3djc

Hi.

Is there a way to remove carriage returns and line feeds from a field in SQL.

Here is what I have at the moment:

CAST(m.Description AS VARCHAR(8000))AS "Product Description"

(have had to cast as VarChar because of problems with aggregate functions in rest of query).

Problem is that this m.description field has carriage returns and line breaks in it .. how do I remove these inside the SQL statement?

thanks

Dec 21 '06 #1

almaz

Have you tried REPLACE ( StringExpression1 , StringExpression2 , StringExpression3 ) T-SQL function?

Dec 22 '06 #2

ics3djc

I tried something like this:

CAST(replace(m.Description, vlbf, ' ') AS VARCHAR(8000))AS "Product Description",

and tried variations of vlbf such as chr(10) chr(13) or \r\n and none of them worked.

I can't put them in single quotes as SQL will look for the actual occurence in the string rather than what it stands for.

I get the error column vlbf is unknown or chr(10) is an unknown function....

Dec 22 '06 #3

ics3djc

sorted it now..... used this in the end:

REPLACE(CAST(m.Description AS varChar(1000)),char(13) + char(10),' ') AS "Product Description",

Thanks for the help

Dec 22 '06 #4