Search for phone numbers in a database that ignore special characters

Question

I have a customer database table where customer phone numbers are stored in a field called phoneNumber.

customerId | customerName | phoneNumber 1 Maulik 0213-383030 2 Maulik1 0-213-383030 3 Maulik2 (0213) 383030 

I want to look for customers with the same phone numbers.

phone numbers can have the characters '-', '(', ')', SPACE. I want to ignore all characters except numbers during the search.

As shown in the database, when I want to find the phone number "0213383030", all these clients should be in the result set.

Can you offer me a request for this.

Answer 1

Method 1 You can use the replacement in the search query. For example: "Select * from the client, where replace (phoneNumber, '-', '') = '212-232-3333' The disadvantage of this method is that you will not be able to use any index, and the search will be (very) slow.

Method 2 In a separate column of the table also keep a clean version of the phone (the artificial column is "phoneNumberClean"), which does not have special characters. When you update records in the main column, also update them in the "clean" column.

Method 3 The third method is to create an index based on functions, which is possible in Oracle. It allows you to search by method 1 without the artificial column of method 2 and have a quick, indexed search. But if you use MySQL, then you cannot use this method because MySQL does not support function-based indexes. Then your best option is to use parameter 2 (artificial column) and use the update trigger.

Answer 2

You can use the REGEXP operator (or is it a synonym for RLIKE) in the WHILE clause. For a regular expression, enter [^0-9]* between each digit of the number you want to find. For instance:

 SELECT * FROM customers WHERE phoneNumber RLIKE '[^0-9]*0[^0-9]*2[^0-9]*1[^0-9]*3[^0-9]*3[^0-9]*8[^0-9]*3[^0-9]*0[^0-9]*3[^0-9]*0[^0-9]*' 

This is terrible, but it should work.

Answer 3

You can clear the phone number before comparing it:

 select * from table where replace(replace(replace(replace(phoneNumber,'('), ')'),' '), '-') = '0213383030'; 

Instead, I would add another column to the table containing the phone number, but without the characters you don't want, then use this column for comparison.

Answer 4

Based on Ted Hopp answer .

PHP script to prepare the search string:

 $str = '123-45-67'; // remove all except digits $maybe_phone = preg_replace("/[^0-9]+/", "", $str); // split string into array $digit_arr = str_split($maybe_phone); // put [^0-9]* around each digit of the number we want to find $phone_regexp = implode('[^0-9]*', $digit_arr); $phone_regexp = '[^0-9]*' . $phone_str . '[^0-9]*'; 

SQL

 SELECT * FROM customers WHERE phoneNumber RLIKE $phone_regexp 

It works for all types of phone number forms stored in the database, and for any search string.