Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

convert database table phone numbers to one standard

Posted on 2004-09-22
18
Medium Priority
?
267 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

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 informatio…
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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

571 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