#1
  1. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,356
    Rep Power
    24

    Need a query to remove all but numbers


    I have 2 phone number columns. Whoever wrote the initial program had not restrictions or validation on what was entered, so it has random spacing, hyphens, parenthesis, n even a rare + sign. I want to remove anything that is not a number so all remaining values should be either 7, 10, or 11-digit numbers. How would I got about doing this?
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,585
    Rep Power
    4287
    Originally Posted by Triple_Nothing
    How would I got about doing this?
    with an application language like php, unfortunately

    mysql's REGEX cannot update, only find
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,585
    Rep Power
    4287
    moved to php forum at OP's request
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,356
    Rep Power
    24
    I found a little something that kinda worked, but only defined items 1 at a time to remove. Granted this is a phone number field, so the characters I removed were +()- and space. I figure a more complete cleaning would be great in case more was typed in. Thanks for any input!

    Code:
    UPDATE ifr SET Telephone = REPLACE(Telephone,'-','')
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1016
    As already pointed out, grab the target rows with MySQL using a regex, then replace the characters with PHP and finally update the value in the database.

    That's 5 lines of PHP code.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Location
    Sydney Australia
    Posts
    215
    Rep Power
    86
    Originally Posted by Triple_Nothing
    even a rare + sign.
    + is shorthand for "International Access code".
    So you can write your Australian international number as something like +61 2 3456 7890, which in Australia would mean 0011 61 etc, but in USA would mean 011 61 etc.

    So, + is good. Don't remove it without replacing it with YOUR local International Access Code or you'll break the number.
  12. #7
  13. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,356
    Rep Power
    24
    So, + is good. Don't remove it without replacing it with YOUR local International Access Code or you'll break the number.
    I am not wishing to avoid the +. The DB is just to hold a single strand of numbers, and they get formatted when placed onto the page, so the + is pretty much only for View mode.
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.

IMN logo majestic logo threadwatch logo seochat tools logo