SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict
    Join Date
    Nov 2002
    Location
    Maryland
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Update phone number format in database

    In the database I have about 100 db records with the phone number in this format: 000.000.000

    I need to update the phone number format to: (000) 000-0000

    Is there a quick way I could do this without changing each record manually? Is their a regular expression that could solve this problem?

  2. #2
    SitePoint Guru
    Join Date
    Jul 2005
    Location
    Orlando
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If your phone number is consistent, you could use substring to grub the number parts.

    http://dev.mysql.com/doc/refman/5.0/...unction_substr

    FWIW, I always store phone numbers as just digits and format when displaying -- cause clients change their mind.

  3. #3
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    UPDATE table SET phone_number = CONCAT(
        '(',
        SUBSTR(phone_number, 1, 3),
        '.',
        SUBSTR(phone_number, 5, 3),
        '.',
        SUBSTR(phone_number, 9, 3),
        ')'
    )
    Something like that?

  4. #4
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, my mistake:

    Code:
    UPDATE table SET phone_number = CONCAT(
        '(',
        SUBSTR(phone_number, 1, 3),
        ')',
        ' ',
        SUBSTR(phone_number, 5, 3),
        '-',
        SUBSTR(phone_number, 9, 3)
    )
    Something like that?

  5. #5
    SitePoint Addict
    Join Date
    Nov 2002
    Location
    Maryland
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    risoknop,
    After I made a small change to the third substr function: SUBSTR(phone_number, 9, 4) it worked! Thank you.

    UFTimmy, Thank you for your response and your recommendation.

  6. #6
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'd also recommend formatting on display. You can write a format_phone() function in PHP to pass the values through before display, which is more flexible than altering your data.

  7. #7
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,869
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    What do you do with the 95% of phone numbers that don't fit that format?
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,339
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    he doesn't gots any o' dose, felgall
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,869
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937 View Post
    he Doesn't Gots Any O' Dose, Felgall
    Yet!
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •