Dear Friends,
Hi, I am facing very big problem but i think it's not big problem for expert. my problem is below,
i want remove all enter from my address filed and it type is TEXT
Thank you,
Qainan IdrisHi Qainan ,
Do you want to remove the e-mail address from the address book and type it in yourself?when do you want to do this ?whilst setting up an e-mail operator? all you need to type is the e-mail address for example , where it ask you for the operators e-mail address all then you can type e.g me@.me.com adn then test it should work , it worked for me.
I am not sure if this is what you meant , i hope this helps , if it doesnt pls reply ..
Regards
Burner|||Depending on whether you use char(13) + char(10) or just char(13) for the [Enter] -- can't you just UPDATE using the Left function and the Len(text)-2 or Len(text)-1 ?|||Use the replace function.|||Unfortunaly, function replace does not work with text field...
Try this one...
create table address(id int,address text null)
insert address
select 3,'12345678
Red Drive
Bigtown
NY
dr
45098'
It needs to fire couple times this batch to replace all 'Enters':
declare @.rt varchar(4)
set @.rt='%'+char(13)+char(10)+'%'
update address
set address=ltrim(substring(address,1,patindex(@.rt,add ress)-1)+' '+
substring(address,patindex(@.rt,address)+2,DATALENG TH(address)))|||snail: Not quite - that is why we have the cast/convert functions.
qainan: The replace function works fine but you will have to nest a cast/convert function with your text field
Showing posts with label address. Show all posts
Showing posts with label address. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
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),
>
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),
>
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:
>
In[vbcol=seagreen]
carriage[vbcol=seagreen]
>.
>|||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
>
>Peter wrote:
>
(10),[vbcol=seagreen]
>
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:
>
In[vbcol=seagreen]
carriage[vbcol=seagreen]
>.
>|||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
>
>Peter wrote:
>
(10),[vbcol=seagreen]
>
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<br>
<br>
replace(t1.address, char(13)+char(10), char(32))<br>
<br>
as Mark suggested? It works for me (see example below).<br>
<br>
declare @.tmp varchar(100)<br>
set @.tmp = 'line1<br>
line2<br>
line3<br>
line4<br>
line5'<br>
<br>
select @.tmp<br>
select replace(@.tmp, char(13)+char(10), char(32))<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
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=""><!-->In
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">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
</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
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<br>
<br>
replace(t1.address, char(13)+char(10), char(32))<br>
<br>
as Mark suggested? It works for me (see example below).<br>
<br>
declare @.tmp varchar(100)<br>
set @.tmp = 'line1<br>
line2<br>
line3<br>
line4<br>
line5'<br>
<br>
select @.tmp<br>
select replace(@.tmp, char(13)+char(10), char(32))<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
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=""><!-->In
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">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
</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
Monday, March 26, 2012
Remove "The report is accessible at the following address:" text
I am sending emails to rpts via links. "The report is accessible at the
following address:" appears above the link. Is there a simple way to have
that text excluded from the email?
Thanks in advance
SPOThere is no way to remove this text. You would have to write your own
delivery extension.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"iamspo" <iamspo@.discussions.microsoft.com> wrote in message
news:397BC3BE-E873-4193-83A4-25EF7B0FF772@.microsoft.com...
> I am sending emails to rpts via links. "The report is accessible at the
> following address:" appears above the link. Is there a simple way to have
> that text excluded from the email?
> Thanks in advance
> SPO
following address:" appears above the link. Is there a simple way to have
that text excluded from the email?
Thanks in advance
SPOThere is no way to remove this text. You would have to write your own
delivery extension.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"iamspo" <iamspo@.discussions.microsoft.com> wrote in message
news:397BC3BE-E873-4193-83A4-25EF7B0FF772@.microsoft.com...
> I am sending emails to rpts via links. "The report is accessible at the
> following address:" appears above the link. Is there a simple way to have
> that text excluded from the email?
> Thanks in advance
> SPO
Tuesday, March 20, 2012
Remote server configuration
hi,
can u help me in configuring a remote server using client-network utility
I got the username, password and the IP address of the server.
regards
In client network utility, you may have to add a TCP/IP alias, if the remote server cannot be reached directly by name.
Then use sp_addlinkedserver, and sp_addlinkedsrvlogin to create a linked server. See SQL Server Books Online for more information and examples.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Aneesh" <aneesh.r@.eostek.com> wrote in message news:OncMDTJlEHA.1652@.TK2MSFTNGP09.phx.gbl...
hi,
can u help me in configuring a remote server using client-network utility
I got the username, password and the IP address of the server.
regards
can u help me in configuring a remote server using client-network utility
I got the username, password and the IP address of the server.
regards
In client network utility, you may have to add a TCP/IP alias, if the remote server cannot be reached directly by name.
Then use sp_addlinkedserver, and sp_addlinkedsrvlogin to create a linked server. See SQL Server Books Online for more information and examples.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Aneesh" <aneesh.r@.eostek.com> wrote in message news:OncMDTJlEHA.1652@.TK2MSFTNGP09.phx.gbl...
hi,
can u help me in configuring a remote server using client-network utility
I got the username, password and the IP address of the server.
regards
Monday, February 20, 2012
Remote connection stopped working
My new and my old applications used to work remotely without problems. Using VS 2003.
Both could connect using the server name or ip address.
Both could connect using the server name or ip address.
Now neither one works now, meaning they can't connect.
Message is that the server doesn't exist.
I can only connect using the server name now, NOT the ip address.
I even tried connection with fire wall off.
Do you mean that you can access the server using the servername but not the IP Address?|||Please follow the instruction at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1 and give us more details if it does not solve your problem.
Subscribe to:
Posts (Atom)