Solved

convert database table phone numbers to one standard

Posted on 2004-09-22
18
254 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
  • 9
  • 6
  • 2
  • +1
18 Comments
 
LVL 4

Expert Comment

by:Kaarthick
Comment Utility
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
Comment Utility
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
Comment Utility
oh yea sorry field size is 50
0
 
LVL 31

Expert Comment

by:alorentz
Comment Utility
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
Comment Utility
wow, i can't believe i didn't think of that, it makes so much sense
0
 
LVL 4

Author Comment

by:LTY83
Comment Utility
let me test it out.
0
 
LVL 31

Expert Comment

by:alorentz
Comment Utility
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
Comment Utility
@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
Comment Utility
Good point, the idea in the code above is to "replace" anything you don't want first...
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 19

Expert Comment

by:peh803
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Should be:
  phone = replace(rs("phone"), "(", "")
  phone = replace(phone, ")", "")
  phone = replace(phone, "-", "")
0
 
LVL 4

Author Comment

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

Expert Comment

by:alorentz
Comment Utility
Glad to help!
0
 
LVL 4

Author Comment

by:LTY83
Comment Utility
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
Comment Utility
nevermind i got it :)
0
 
LVL 31

Expert Comment

by:alorentz
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now