Replacing carriage return in SQL 1

[meydz]

is there anyone who knows how to replace a string containing carriage return with space.
ex:
declare @a as varchar
set @a = 'the quick
brown fox'

--the output
print @a
the quick
brown fox

--i want the output in one line
the quick brown fox
any help would be appreciated.
thanks in advance

 

[moonshadow]

I only got your example to work by declaring @a as varchar(40). I guess SQL defaults to varchar(1). Anyhow, you'll need to use the replace function, to replace the carriage return with a space. In this example, as in many others, the carriage return is char(13) + Char(10) - technically a carriage return & line feed. So,
set @a=replace(@a, char(13) + char(10), ' ')
will work in this case. However, you may have just a lone char(13) or char(10) in your string, so to cover this you might want to add the following two lines:
set @a=replace(@a, char(13), ' ')
set @a=replace(@a, char(10), ' ')
You still have to have the first line, or you'll end up with two spaces in some circumstances.
Hope that's what you needed.

 

[moonshadow]

I only got your example to work by declaring @a as varchar(40). I guess SQL defaults to varchar(1). Anyhow, you'll need to use the replace function, to replace the carriage return with a space. In this example, as in many others, the carriage return is char(13) + Char(10) - technically a carriage return & line feed. So,
set @a=replace(@a, char(13) + char(10), ' ')
will work in this case. However, you may have just a lone char(13) or char(10) in your string, so to cover this you might want to add the following two lines:
set @a=replace(@a, char(13), ' ')
set @a=replace(@a, char(10), ' ')
You still have to have the first line, or you'll end up with two spaces in some circumstances.
Hope that's what you needed.

 

[meydz]

its working!
thanks a lot
before i posted this thread i tried replacing the string with Char(13) only but i did not get the expected result.

thanks for your help!
meydz