Solved

convert database table phone numbers to one standard

Posted on 2004-09-22
18
255 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
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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How can i convert a Generic list to DataTable in c# 3 772
Out the count variables 6 53
Scheduled IIS .Net2 AppPools recycle and SQL connection Hangs 33 91
asp syntax 3 38
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
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/…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

895 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

15 Experts available now in Live!

Get 1:1 Help Now