Solved

convert database table phone numbers to one standard

Posted on 2004-09-22
18
260 Views
Last Modified: 2008-02-01
Hello everyone,

I have a possibly difficult question involving sql, and maybe asp, depending on how you want to answer it.

basically I would like to be able to have a script that goes through and formats all phone numbers as (xxx) xxx-xxxx
in my customer table of my mysql db. I'm not quite sure how to tackle this considering the phone numbers are in all different formats, some including area code, some not, some including 1's at thebegining some not. If this is possible it would save me a great amount of time and make my search engine a lot better. The only thing I can think of is if the phone number is less than 7 digits (bad data) to not change the format, and if the phone number is more than 11 digits (bad data) to not change the digits as well. As far as i can see, correct phones could have 7 digits, 10 digits (includes area code), or 11 digits (includes 1 and area code, also 800/888 #'s). I believe as far as I can think anything other than those would be considered bad data.

Any thoughts?

Thanks!

- Loren
0
Comment
Question by:LTY83
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 2
  • +1
18 Comments
 
LVL 4

Expert Comment

by:Kaarthick
ID: 12122221
Can u tell what datatype you used previously for the phone number and what is the size of that field?
0
 
LVL 4

Author Comment

by:LTY83
ID: 12122251
datatype: varchar
it seems that the previous format(not on all numbers but majority) is: (630)515-1883
however i'm looking for (xxx) xxx-xxxx

i know anal retentive
0
 
LVL 4

Author Comment

by:LTY83
ID: 12122257
oh yea sorry field size is 50
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 31

Expert Comment

by:alorentz
ID: 12122274
DO until rs.eof
  phone = rs("phone")
  l = len(phone)
  update = 0
  if l = 7 then
       update=1
       phone = "(xxx)" & left(phone,3) & "-" & right(phone, 4)
  elseif l = 10 then
      update=1
      phone = "(" & left(phone, 3) & ")" & mid(phone, 4, 3) & "-" & right(phone, 4)
  elseif l =11 then
     update=1
     phone = "(" & mid(phone, 2, 3) & ")" & mid(phone, 5, 3) & "-" & right(phone, 4)
  end if

  if update = 1 then
      rs("phone") = phone
      rs.update
  end if
     rs.movenext
loop


presto!!!
0
 
LVL 4

Author Comment

by:LTY83
ID: 12122279
wow, i can't believe i didn't think of that, it makes so much sense
0
 
LVL 4

Author Comment

by:LTY83
ID: 12122282
let me test it out.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 12122287
Oh, need to remove existing () and - so....

DO until rs.eof
  phone = replace(rs("phone"), "(")
  phone = replace(phone, ")")
  phone = replace(phone, "-")
  l = len(phone)
  update = 0
  if l = 7 then
       update=1
       phone = "(xxx)" & left(phone,3) & "-" & right(phone, 4)
  elseif l = 10 then
      update=1
      phone = "(" & left(phone, 3) & ")" & mid(phone, 4, 3) & "-" & right(phone, 4)
  elseif l =11 then
     update=1
     phone = "(" & mid(phone, 2, 3) & ")" & mid(phone, 5, 3) & "-" & right(phone, 4)
  end if

  if update = 1 then
      rs("phone") = phone
      rs.update
  end if
     rs.movenext
loop
0
 
LVL 19

Expert Comment

by:peh803
ID: 12122308
@alorentz -- I know I got into this post late, so I'm by no means try to steal any thunder (you've def. got this one), but you might also consider replacing any spaces in the stored phone number (just in case)...

peh803
0
 
LVL 31

Expert Comment

by:alorentz
ID: 12122332
Good point, the idea in the code above is to "replace" anything you don't want first...
0
 
LVL 19

Expert Comment

by:peh803
ID: 12122373
yup, I agree.  Basically, you're stripping whatever the user entered into the database down to the bare bones numbers so you can then have your way with them...

enough of my $0.02, you've definitely got this one handled (and with the "right" answer, nonetheless).
peh803
0
 
LVL 4

Author Comment

by:LTY83
ID: 12122395
hmmm i seem to get this error:
Wrong number of arguments or invalid property assignment: 'replace'

at this line:
  phone = replace(rs("phone"), "(")

any suggestions? :)
0
 
LVL 31

Accepted Solution

by:
alorentz earned 500 total points
ID: 12122430
Whoops:

DO until rs.eof
  phone = replace(rs("phone"), "(", "")
  phone = replace(phone, ")", "")
  phone = replace(phone, "-", "")
 phone = replace(phone, " ", "")
  l = len(phone)
  update = 0
  if l = 7 then
       update=1
       phone = "(xxx)" & left(phone,3) & "-" & right(phone, 4)
  elseif l = 10 then
      update=1
      phone = "(" & left(phone, 3) & ")" & mid(phone, 4, 3) & "-" & right(phone, 4)
  elseif l =11 then
     update=1
     phone = "(" & mid(phone, 2, 3) & ")" & mid(phone, 5, 3) & "-" & right(phone, 4)
  end if

  if update = 1 then
      rs("phone") = phone
      rs.update
  end if
     rs.movenext
loop
0
 
LVL 4

Author Comment

by:LTY83
ID: 12122440
Should be:
  phone = replace(rs("phone"), "(", "")
  phone = replace(phone, ")", "")
  phone = replace(phone, "-", "")
0
 
LVL 4

Author Comment

by:LTY83
ID: 12122476
ok thanks a ton man, awarded you points, thanks for the help this should work great! :)
0
 
LVL 31

Expert Comment

by:alorentz
ID: 12122509
Glad to help!
0
 
LVL 4

Author Comment

by:LTY83
ID: 12122544
alorentz, one more quick question, is there a way to detect letters in the phone number field and skip those fields?
0
 
LVL 4

Author Comment

by:LTY83
ID: 12122579
nevermind i got it :)
0
 
LVL 31

Expert Comment

by:alorentz
ID: 12122599
Look into regex...This will strip all letter and other things (except dashes?).....
<%
sString = "1-(609)-544-0442 Phone Number"
Response.Write "Search string:" &sString

Set RegX = NEW RegExp
SearchPattern = "[^0-9_\-]"
RegX.Pattern = SearchPattern
RegX.Global = True
ReplacedText = RegX.replace(sString, "" )

Response.Write "<p>New String: "&ReplacedText
%>
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question