SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member Victor's Avatar
    Join Date
    Dec 2000
    Location
    Georgia
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Update Query? "stripping extra chars" from phone field.

    Is there a method, in MySQL, to strip all non-digits in a phone number field, where the numbers are currently inconsistently formatted?


    For example,

    Code:
    UPDATE temp SET work_phone=method(work_phone);
    
    (123) 345-3434    would become    1233453434
    324-343-3454      would become    3243433454
    300 000 0032      would become    3000000032
    404/123/0032      would become    4041230032
    etc...

    I'll continue to search, but I'd be grateful for any assistance.

    Thanks!

    Vic
    Last edited by Victor; Aug 25, 2001 at 16:08.

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I know that mysql has the function REGEX which will match a value against a regular expression, but I don't know of a mysql function that will find/replace against a regular expression. That's not to say there isn't one.

    So, (assuming you are using php or perl) you will want to use a regular expression replace funciton (eg, in php - ereg_replace or preg_replace) to perform this before you insert/update data into the database.

    Problem is - I don't really know regular expressions very well. I am sure someone who is more skilled will come up with something 4 u.

  3. #3
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this should strip out all non digits:

    PHP Code:
    $phone_number preg_replace('/[^0-9]+/'''$phone_number); 
    try that in the PHP before INSERTing into the table.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR


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
  •