Remove Carriage Return

I attempt to replace the Carriage Return with Space. In
this way, in the SQL Statement, I add this script:
replace(t1.address, char(13), char(32))
However, it seems that it only remove the first carriage
return but not all. Is there any suggestion ?
Thanks
try removing the line feed character as well - char(10)
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Peter" wrote:

> I attempt to replace the Carriage Return with Space. In
> this way, in the SQL Statement, I add this script:
> replace(t1.address, char(13), char(32))
> However, it seems that it only remove the first carriage
> return but not all. Is there any suggestion ?
> Thanks
>
|||Does it mean that I have to run like this
replace(replace(t1.address, char(13), char(32)), char(10),
char(32)) ?
Thanks
[vbcol=seagreen]
>--Original Message--
>try removing the line feed character as well - char(10)
>
>regards,
>Mark Baekdal
>http://www.dbghost.com
>http://www.innovartis.co.uk
>+44 (0)208 241 1762
>Database change management for SQL Server
>
>"Peter" wrote:
In[vbcol=seagreen]
carriage
>.
>
|||Have you tried
replace(t1.address, char(13)+char(10), char(32))
as Mark suggested? It works for me (see example below).
declare @.tmp varchar(100)
set @.tmp = 'line1
line2
line3
line4
line5'
select @.tmp
select replace(@.tmp, char(13)+char(10), char(32))
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Peter wrote:
[vbcol=seagreen]
>Does it mean that I have to run like this
>replace(replace(t1.address, char(13), char(32)), char(10),
>char(32)) ?
>Thanks
>
>In
>
>carriage
>
|||Thank you for your advice.
When I use char(13)+char(10), most of the problems are
fixed.
However, for unknown reason, there is record that works
fine with char(13) only becomes like this:
Aboukir Street
MONGKOK ABN 3355
Do you have any suggestion ?
Besides, when I use Notepad, is it possible for me to see
whether it is only CR or CRLF ?
Thanks
>--Original Message--
>Have you tried
> replace(t1.address, char(13)+char(10), char(32))
>as Mark suggested? It works for me (see example below).
>declare @.tmp varchar(100)
>set @.tmp = 'line1
>line2
>line3
>line4
>line5'
>select @.tmp
>select replace(@.tmp, char(13)+char(10), char(32))
>--
>*mike hodgson* |/ database administrator/ | mallesons
stephen jaques
>*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61
(408) 675 907
>*E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
http://www.mallesons.com[vbcol=seagreen]
>
>Peter wrote:
(10),
>