Remote connection failed: 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 ?
Thankstry 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
>--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:
>> 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
>.
>|||This is a multi-part message in MIME format.
--060207020402090904070407
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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:
>Does it mean that I have to run like this
>replace(replace(t1.address, char(13), char(32)), char(10),
>char(32)) ?
>Thanks
>
>>--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:
>>
>>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
>>
>>.
>>
--060207020402090904070407
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>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)) 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Peter wrote:
<blockquote cite="mid0ea101c518a6$4f165ab0$a401280a@.phx.gbl" type="cite">
<pre wrap="">Does it mean that I have to run like this
replace(replace(t1.address, char(13), char(32)), char(10),
char(32)) ?
Thanks
</pre>
<blockquote type="cite">
<pre wrap="">--Original Message--
try removing the line feed character as well - char(10)
regards,
Mark Baekdal
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.dbghost.com</a>">http://www.dbghost.com">http://www.dbghost.com</a>
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.innovartis.co.uk</a>">http://www.innovartis.co.uk">http://www.innovartis.co.uk</a>
+44 (0)208 241 1762
Database change management for SQL Server
"Peter" wrote:
</pre>
<blockquote type="cite">
<pre wrap="">I attempt to replace the Carriage Return with Space.
</pre>
</blockquote>
</blockquote>
<pre wrap="">carriage
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">return but not all. Is there any suggestion ?
Thanks
</pre>
</blockquote>
<pre wrap="">.
</pre>
</blockquote>
</blockquote>
</body>
</html>
--060207020402090904070407--|||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
>
>Peter wrote:
>>Does it mean that I have to run like this
>>replace(replace(t1.address, char(13), char(32)), char
(10),
>>char(32)) ?
>>Thanks
>>
>>--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:
>>
>>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
>>
>>.
>>
>sql

Wednesday, March 28, 2012

Remove Carriage Return

No comments:

Post a Comment

Remote connection failed

Blog Archive

About Me