Resistance Resistance - 3 months ago 21
SQL Question

Compare phone numbers of multiple formats in SQL Server

I want to begin by saying that this question here sort of helps but my issues is a bit different.

I have two tables. The first one has:

| AREACODE| PHONENUMBER | Company Name |
===========================================
| 800 | 115-4454 | Sample Company |
| 800 | 141-2254 | Sample Company2 |
| 800 | 113-9857 | Sample Company3 |


The second table has:

|PHONENUMBER|
=============
|18001154454|
| 7274313 |


This formatting in the second table makes it really hard for me as someone not great in SQL to match the items across tables.

The difference in my question compared to the linked on is I can't alter the tables in any way.

The full requirement is that when a area code + phone number in table 1 match with the phone number in table 2, I need to select a column called company name.

I considered a solution using CASE WHEN to check the lengths and do complicated substring modifications but I doubt it would be the proper method. Any help would be appreciated. If I am unclear on anything please ask and I will do my best to clarify for you.

EDIT

All columns are strings.

The expected results would be the company name of any matches between the tables for the phone number and the phone number in it's original format from the first table (area code + phonenumber)

So from the above example I would expect

PHONENUMBER | Company Name |
===================================
800-115-4454 | Sample Company |

Answer

Assuming these are all string columns, you can get rid of the - with REPLACE and combine the separate columns to compare with the single column:

SELECT     CompanyName
FROM       SingleColumnTable S
INNER JOIN MultiColumnTable M on M.AreaCode + REPLACE(M.PhoneNumber,'-','') = S.PhoneNumber

In the case where there is a leading one:

INNER JOIN MultiColumnTable M 
   ON M.AreaCode + REPLACE(M.PhoneNumber,'-','') = S.PhoneNumber
   OR '1' + M.AreaCode + REPLACE(M.PhoneNumber,'-','') = S.PhoneNumber

In the case there is no area code:

INNER JOIN MultiColumnTable M 
   ON M.AreaCode + REPLACE(M.PhoneNumber,'-','') = S.PhoneNumber
   OR '1' + M.AreaCode + REPLACE(M.PhoneNumber,'-','') = S.PhoneNumber
   OR REPLACE(M.PhoneNumber,'-','') = S.PhoneNumber

Full Query:

SELECT     M.AreaCode + '-' + M.PhoneNumber "PhoneNumber", M.CompanyName
FROM       SingleColumnTable S
INNER JOIN MultiColumnTable M 
   ON M.AreaCode + REPLACE(M.PhoneNumber,'-','') = S.PhoneNumber
   OR '1' + M.AreaCode + REPLACE(M.PhoneNumber,'-','') = S.PhoneNumber
   OR REPLACE(M.PhoneNumber,'-','') = S.PhoneNumber

Disclaimer: This will probably perform like absolute trash and hopefully is not something you will need to use repeatedly.

Comments