Eliminating commas within a concatenated string

[marydn]

Hi,

I concatenate an address in SQL and when I call the data in the ASP page I see all the commas where there is no address. I want to eliminate the commas.

For example:

123 Elm Street, Miami, FL 20333

If there is no adress it shows as:
, ,
on the page.

I am sure this an easy fix, but not for me....

THANKS!

 

[medic]

Try this:

<%
Function TrimAddr(mystr)
  svaddr=&quot; &quot;+mystr
  do while instr(svaddr,&quot; ,&quot;)>0
    svaddr=Replace(svaddr, &quot; ,&quot;,&quot;&quot;)
  loop
  TrimAddr=svaddr
End Function

MyAddr=&quot;123 Elm Street, Miami, FL 20333&quot;
response.write(&quot;'&quot;+TrimAddr(MyAddr)+&quot;'<br>&quot;)

MyAddr=&quot;, , &quot;
response.write(&quot;'&quot;+TrimAddr(MyAddr)+&quot;'<br>&quot;)
%>

 

[rac2]

So you have a string that may consist of ",,", or ", ,".

You could handle this in the ASP script like so.

Dim strAddress
strAddress = rsGet.Fields("address").Value
If strAddress = ",," Then
   strAddress = " "
EndIf

Or you could handle it in SQL like so.

SELECT 
   CASE
      WHEN addr_line_1 = "" THEN ""
      WHEN addr_line_2 = "" THEN addr_line_1 + ", " + city + ", " + state + "  " + zip
      ELSE addr_line_1 + addr_line_2 + ", " + city + ", " + state + "  " + zip
   END AS "address"

Actually that might not be the best logic but you get the idea.

 

[onpnt]

just for kicks even knowing I doubt I'd create the object for a comma (however you can pass the comma and anything else to the a function as this and then it would be worth it)

Set regObj = New RegExp
regObj.pattern = ","
regObj.global = true
str = "string, that, may, consist, of ,,, or ,,,,, ,."
str = regObj.replace(str," &quot

_____________________________________________________________________

http://www.onpntwebdesigns.com