Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

convert database table phone numbers to one standard

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
LTY83
Asked:
LTY83
  • 9
  • 6
  • 2
  • +1
1 Solution
 
KaarthickCommented:
Can u tell what datatype you used previously for the phone number and what is the size of that field?
0
 
LTY83Author Commented:
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
 
LTY83Author Commented:
oh yea sorry field size is 50
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
alorentzCommented:
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
 
LTY83Author Commented:
wow, i can't believe i didn't think of that, it makes so much sense
0
 
LTY83Author Commented:
let me test it out.
0
 
alorentzCommented:
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
 
peh803Commented:
@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
 
alorentzCommented:
Good point, the idea in the code above is to "replace" anything you don't want first...
0
 
peh803Commented:
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
 
LTY83Author Commented:
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
 
alorentzCommented:
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
 
LTY83Author Commented:
Should be:
  phone = replace(rs("phone"), "(", "")
  phone = replace(phone, ")", "")
  phone = replace(phone, "-", "")
0
 
LTY83Author Commented:
ok thanks a ton man, awarded you points, thanks for the help this should work great! :)
0
 
alorentzCommented:
Glad to help!
0
 
LTY83Author Commented:
alorentz, one more quick question, is there a way to detect letters in the phone number field and skip those fields?
0
 
LTY83Author Commented:
nevermind i got it :)
0
 
alorentzCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now