#1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    724
    Rep Power
    7

    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 and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    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,280
    Rep Power
    4279
    moved to php forum at OP's request
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    724
    Rep Power
    7
    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 and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    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
    189
    Rep Power
    84
    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
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    724
    Rep Power
    7
    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 and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him

IMN logo majestic logo threadwatch logo seochat tools logo