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

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.

Remiz

Remixed version of unstable human emotions and thirst of mankind actions. UX designer, UI developer and HE of WebCastle Media Pvt LTD

2 Responses

  1. jafar says:

    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

Talk your view

%d bloggers like this:
Read previous post:
Easiest way to type Malayalam – Easy Malayalam Software Download

My friend Anoop MC's Easy Malayalam Software hit 2 million download. Lets celebrate.

Close