SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    stripping characters within query ?

    Help !!

    I have a table with 2 fields holding phone numbers 'Home' and 'Mobile' over the years these have been populated with no standardisation on format, so I have

    ++33 0562346789
    33 (0)562346789
    +33.(0)5.62.34.67.89

    you get the picture

    Now I need to be able to search by phone number (or part phone number) so I need to strip out all non numerical characters so I can perform a LIKE with the inputed string. Can I do this on the fly within the query ?
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Mandes View Post
    Can I do this on the fly within the query ?
    yes

    use the mysql REPLACE function, nested as many times as necessary
    Code:
    WHERE REPLACE(
          REPLACE(
          REPLACE(
          REPLACE(phone,'(','')
                       ,')','')
                       ,'.','')
                       ,'+','') LIKE '%937%'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy, was kind of hoping there was a more complete solution like a regex to remove everything but numbers, but thats working OK.
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    I'm not aware of a way. You may want to consider in whatever server-side language is in use for each phone number in turn, explode it into an array (1 character per array element). Then for each element of the array check to see if it's numerical, if it's not remove it from the array, once all elements have been examined, combine them back into a string and update the phone field in the database with the sanitized phone number.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    SpacePhoenix - yes I was thinking last night that would probably be the way to go, the client still wants to to be able to format the numbers using some symbols, so I'll set a standard and sanitise on input, then write a script to strip these out in php as they are entered to the database, I'll probably create one new field with both numbers stripped and pasted together then use that for my searching.
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming


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
  •