4

I have a database table of customers where customer's phone numbers are stored in a field named phoneNumber.

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

I want to search customers having same phone numbers.

phone numbers might have '-' , '(', ')' , SPACE characters. I want to ignore all characters except numbers while searching.

As shown in database, when I want to search phone number '0213383030', these all customers should be in resultset.

Can you suggest me query for this.

vatavale
  • 1,470
  • 22
  • 31
Maulik Vora
  • 2,544
  • 5
  • 28
  • 48
  • For the friends who are coming from the search engine for this question. I am finally going to create a new column with cleaned contacts as suggested by 'lal00' and 'Josh' as I have lacs of rows and I have to prefer performance-improvement over headache for current project, But you can use also 'RLIKE' or 'replace' If you have small numbers of rows in table as suggested by 'lal00' and 'Ted Hopp'. Thanks to all genius here. – Maulik Vora Mar 23 '11 at 07:08

4 Answers4

4

You can use the REGEXP operator (or it's synonym RLIKE) in a WHILE clause. For the regular expression, put in [^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]*'

It's awful, but it should work.

Ted Hopp
  • 232,168
  • 48
  • 399
  • 521
2

You can clean the phoneNumber before comparing it:

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

I would, instead, add another column to the table that contains the phoneNumber but without the characters you don't want and then use that column for comparisons.

edmz
  • 3,350
  • 2
  • 22
  • 29
  • How much impact of this query will be there on query-execution speed, as there are more than one Lac rows. I have index on phoneNumber field – Maulik Vora Mar 23 '11 at 06:59
  • It will have to search all the fields. It is not optimal. My suggestion to add another column with the result of that filtering is the best choice: `update mytable set newPhoneColumn = replace(replace(replace(replace(phoneNumber,'('), ')'),' '), '-');` – edmz Mar 23 '11 at 07:01
2

Method 1: You can use replace in the query to search. For example: "Select * from customer where replace(phoneNumber,'-','') = '212-232-3333' The disadvantage of this method is that you will be unable 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 phone (artificial column - "phoneNumberClean") that doesn't have any special characters. When you update records in main column, also update in the "clean" column.

Method 3: A third method is to creating a function based index, which is possible in Oracle. It allows you to search using method 1, without having an artificial column of method 2, and still have fast, indexed searches. But, if you are using MySQL, then you can't use this method as MySQL does not support function based indexes. Your best option then is to use option 2 (artificial column), and to use an update trigger.

Josh
  • 189
  • 12
1

Based on Ted Hopp answer.

PHP script for preparing 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 kind of phone number forms saved in DB, and for any kind of search string.

vatavale
  • 1,470
  • 22
  • 31