CODEWITHSUNDEEP
mysqlregex
alden
alden

Reputation: 3

Select a row by phone number with inconsistent formatting

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%'

Upvotes: 0

Views: 258

Answers (2)

whiscode
whiscode

Reputation: 2791

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.

Upvotes: 1

ikiK
ikiK

Reputation: 6532

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

Upvotes: 0

Related Questions