MySQL - WHERE IN CLAUSE Regular Expression to Match Phone numbers

Question

I want to search phone numbers from users table, it has million user records. The Users table will have phone numbers with or without country code.

Sample phone numbers in User table

+911111122222
3333344444
+15555566666
+917777788888
+19999900000

The input phone numbers which I will pass, it may or may not have the country code. I will input 100 to 1000 phone numbers

1111122222
+913333344444
+15555566666
7777788888
9999900000

I want to retrieve all records which matches phone numbers in despite of country code.

Right now I am using this,

Select * from users WHERE phone_numer IN ("1111122222","+913333344444", "+15555566666", "7777788888", "9999900000");

It doesn't return all the phone numbers, how should we handle this?

Can we assume phone number with `+` is with country code? how about area code? — Raptor, Aug 01 '13 at 08:48

score 1 · Answer 1 · edited May 23 '17 at 12:14

1

You could possible just check if the number you want to test against is at the end of the number in the database, to do this, check out this question on how to use LIKE with multiple values.

WHERE phone_number LIKE '%1111122222'

will match both 1111122222 and +91111122222

edited May 23 '17 at 12:14

Community

1
1

answered Aug 01 '13 at 08:48

NDM

6,731
3
39
52

Yes, the highest score answer (not the accepted one) suits OP's case. Adding `SUBSTRING` will be better – Raptor Aug 01 '13 at 08:50
I will input 100 to 1000 Phone numbers. – gvm Aug 01 '13 at 08:54

Prahalad Gaggar · Answer 2 · 2013-08-01T08:56:53.573

1

select *
from users
where 
CASE WHEN phone_number like '+%' THEN SUBSTRING(phone_number,4,len(phone_number))
ELSE phone_number IN (Pass phone numbers without code)

Example:

select *
from users
where 
CASE WHEN phone_number like '+%' THEN SUBSTRING(phone_number,4,length(phone_number))
ELSE phone_number END IN 
('1111122222','3333344444', '555566666', '7777788888', '9999900000')

SQL FIDDLE

edited Aug 01 '13 at 08:56

answered Aug 01 '13 at 08:51

Prahalad Gaggar

11,389
16
53
71

MySQL - WHERE IN CLAUSE Regular Expression to Match Phone numbers

2 Answers2