Select a row by phone number with inconsistent formatting

Question

I have a table which has a field (phone_number (tinytext)) that contains phone numbers in a variety of unpredictable, non-standard formats, just however they were keyed in. Unfortunately, I didn't create nor can I change the data set. Example:

+---+------------------+
| id|    phone_number  |
+---+------------------+
| 1 |  (999) 999-9999  |
| 2 |  +19999999979    |
| 3 |  999-999-9979    |
| 4 | (999)999-9999    |
| 5 |  999/999/9999    |
| 6 |  99-9999-9999    |
| 7 |  (999-9999999    |
+---+------------------+

What I need to be able to do is, given a string of straight digits (i.e., 9999999979), I need to find the row(s) that contain the same digits, in the same order.

So, for my generic example, I want to match row 2 and 3 because they both have all the digits, in order (once non-numeric characters are removed). Basically I need a way to match but first remove any non-numeric characters.

I have tried REGEXP, REGEX_REPLACE, FIND_IN_SET, but can't seem to make any of them work.

I am looking for something like this (which of course doesn't work):

SELECT * from phone_data WHERE phone_number REGEXP '^?![0-9]$' LIKE '%9999999979%'

You can't store formatted strings like that in TINYINT. You shouldn't use integers to store phone numbers, zip codes, credit card numbers, etc. Even though these contain digits, they're not really numbers. — Barmar, Jun 05 '20 at 15:17
`REGEXP` does not do regexp replacement, it returns TRUE or FALSE depending on whether the column matches the regexp. — Barmar, Jun 05 '20 at 15:18
The best idea is to remove all the extra characters before storing into the DB, and store everything in a consistent format. — Barmar, Jun 05 '20 at 15:40

whiscode · Accepted Answer · 2020-06-05T16:41:17.030

1

SELECT * from phone_data WHERE phone_number LIKE '%9%9%9%9%9%9%9%9%7%9%'

Not the cleanest but works for your data set

** EDIT**

As pointed out by Barmar in the comments below, this will also match on values with extra digits in the middle eg 99919998979

Another option could be to use this:

SELECT `phone_number` FROM `phone_data` WHERE REGEXP_REPLACE(`phone_number`, '[^0-9]+', '') REGEXP '9999999979$'

Explanation:

REGEXP_REPLACE(`phone_number`, '[^0-9]+', '')

This removes all non-numeric characters

REGEXP '9999999979$'

This matches the last part of the column value. Of course this is not fool-proof - it doesn't take into account where the area code finishes.

edited Jun 05 '20 at 16:41

answered Jun 05 '20 at 15:31

whiscode

2,301
1
12
7

Note that this will also match if it has extra digits – Barmar Jun 05 '20 at 15:35
Which for OP seems fine since he wants to include row 2. Right? @Barmar – JvdV Jun 05 '20 at 15:38
but it will also allow extra digits in the middle. `99919998979` – Barmar Jun 05 '20 at 15:39
Yes, it will match extra digits in the middle, but should work for my purposes. – alden Jun 05 '20 at 15:59
@Barmar - This is a valid point - see my updated answer. It's still not fool-proof but should work for the majority of cases – whiscode Jun 05 '20 at 16:44

ikiK · Answer 2 · 2020-06-05T15:44:29.563

Maybe you could get away with something like this: BTW I usually strip things like this in PHP before insert.

Btw this is duplicate topic and this .

**Schema (MySQL v5.7)**


    CREATE TABLE phone_data
        (`id` int, `phone_number` varchar(255))
    ;

    INSERT INTO phone_data
        (`id`, `phone_number`)
    VALUES
        (1, '(999) 999-9999'),
        (2, '+19999999979'),
        (3, '999-999-9979'),
        (4, '(999)999-9999'),
        (5, '999/999/9999'),
        (6, '99-9999-9999'),
        (7, '(999-9999999')                                  
    ;

---

**Query #1**

    SELECT * FROM `phone_data` WHERE `phone_number` LIKE '%9%9%9%9%9%9%9%9%7%9%';

| id  | phone_number |
| --- | ------------ |
| 2   | +19999999979 |
| 3   | 999-999-9979 |

---

View on DB Fiddle

Select a row by phone number with inconsistent formatting

2 Answers2