PHPMYADMIN - Phone is NOT LIKE other Phone in two Tables - Select `Phone` from `Phones1` WHERE `Phone` is NOT LIKE '%', `Phone` in `Phones2`

Question

I am trying to do this in the phpmyadmin SQL query box on a cpanel shared server. This works but, Local_Formatted in Phone1 and Phone2 tables are not always properly formatted.

                           Table1
 ---------------------------------------------------------------
| Formatted_Phone|Active_Status|Line_Type|Region|Local_Formatted|
 ---------------------------------------------------------------
|  +11236547890  |      Y      |  Mobile |  CA  | (123)654-7890 |
 ---------------------------------------------------------------
|  1234567890    |      Y      |  Land   |  CA  | 123 456-7890  |
 ---------------------------------------------------------------
|  13214567890   |      Y      |  Mobile |  CA  | 321-456-7890  |
 ---------------------------------------------------------------
|  13214569999   |      Y      |  Mobile |  CA  | 321-456 9999  |
 ---------------------------------------------------------------
|  16664565555   |      Y      |  Mobile |  MI  | 666-456 5555  |
 ---------------------------------------------------------------


             Table2
 --------------------------------
| Formatted_Phone|Local_Formatted|
 --------------------------------
|  11236547890  | (123)654-7890 |
 --------------------------------
|  1234567890   | 123 456-7890  |
 --------------------------------
|  +13214567890 | 321-456-7890  |
 --------------------------------
|  1 1239997890 | 123 999 7890  |
 --------------------------------
|  16664565555  | 666-456 5555  |
 --------------------------------

I want the query to return the row in Table1 with the 321 456 9999 number because all the other numbers do not match. (The Active_Status AND Region are in query too, read on below)

SELECT `Local_Formatted`,`Line_Type` 
FROM `Phone1` 
WHERE `Active_Status` NOT LIKE '%Disconnected%'
 AND `Region` = 'CA'
 AND `Local_Formatted` NOT IN  (SELECT `Local_Formatted` FROM `Phone2`);

Would be great BUT the DB was not populated very well obviously. The person with our Parkinson's charity who was donating his time has passed. Now I am trying to help.

A big problem is, the numbers in the Local_Formatted columns can be: (in both tables)

1 123 456 7890
1(123)-456-7890
1234567890
123-456-7890
(123) - 456 7890
and so on.

I also have another column "Formatted_Phone" where the numbers are like:

+11234567890
11234567890
1234567890

So, I thought that comparing the "Formatted_Phone" column would be the easiest to figure out. I even tried to find a way to just get the last 10 digits \D (left,10) or whatever in the query and comparing that way but, that's way over my head.

SELECT `Local_Formatted`,`Line_Type` 
FROM `Phone1` 
WHERE `Active_Status` NOT LIKE '%Disconnected%'
 AND `Region` = 'CA'
 AND `Formatted_Phone` NOT LIKE (SELECT CONCAT('%', `Formatted_Phone`, '%') FROM `Phone2`);

But that does not work.

#1242 - Subquery returns more than 1 row

I tried IN and MAX and JOIN solutions I found here and researched for days. I feel badly and pretty STUPID that I cannot figure this out. I would like to learn how to solve both problems just out of curiosity and my failures. But, I am sure I will learn something new here today either way.

The desired result is to list the phones (and whatever adjacent cells I ask for) from Table1 in state CA for example, that are not in Table2. The numbers are there but too many other characters/formatting issues prevent a match.

Thanks for any assistance.

Answer 1

The argument to LIKE and NOT LIKE can only be a single string, not a list returned by a subquery. You can use a NOT EXISTS subquery.

AND NOT EXISTS (
    SELECT *
    FROM Phone2
    WHERE Phone1.Formatted_Phone LIKE CONCAT('%', Phone2.Formatted_Phone, '%')
)