Stripping characters within query?

Databases
1

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 ?

2

yes

use the mysql REPLACE function, nested as many times as necessary

WHERE REPLACE(
      REPLACE(
      REPLACE(
      REPLACE(phone,'(','')
                   ,')','')
                   ,'.','')
                   ,'+','') LIKE '%937%'
3

Thanks Rudy, was kind of hoping there was a more complete solution like a regex to remove everything but numbers, but thats working OK.

4

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.

5

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.