Search phone numbers in database ignoring special characters

Question

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.

Answer 1

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.