Search a formatted number in a MySQL table with a differently formatted search word

by Remiz · November 30, 2012

People enter phone number in various formats on the web. Some people just write down the number without space or hyphens. Some does with ( brackets etc.

Sometimes we have to store it as it is to keep the clients /user happy. But its pain when you want to verify the number or do a search with number by entering entirely a different format. Following MySQL query helps you do it with ease.

It strips off all symbols and spaces from the data which is there in entire phone number data in your db and matches it with the query text you enter.

Here is the MySQL query to find formatted phone number by a differently formatted search text.

SELECT * FROM bizz_business_user
WHERE 
TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(phone,
          ' ',   '' ),
          '-',   '' ),
          ',',   '' ),
          '/',   '' ),
          '(',   '' ),
          ')',   '' ),
          '+46', '0')
        ) = '2403473649'

Hope this helps you to do awesome things with phone numbers.

jafar says:

November 30, 2012 at 9:17 pm

helpful 🙂

jafar says:

November 30, 2012 at 9:16 pm

this method is indeed slow(not n hour late :P) .Use “SELECT * FROM `table`
WHERE `numberes` REGEXP ‘1[() -]*9′” (when you search ’19’)
Note You need a [() -]* between each input character.Expected data:+1 (003) 534-2343