Compare sql phone numbers

Question

I have 2 sets of phone numbers in 2 different tables, table 1 has the direct format 01234567890 and the other table has 3 different formats, sometimes it is 01234567890 or 01234 567890 or 01234-567890 .

Currently, I'm just doing an inner join in tables and getting only a few rows, but expect more, since it is obvious that anything with a hyphen or space will be skipped from the join.

As a system, I can’t change the data in a table with 3 formats, so I can’t standardize it or clear it, etc.

What would be the best way to handle this?

Answer 1

Well, you might consider creating a computed column in a second table to normalize the phone number format - something like:

 ALTER TABLE dbo.YourSecondTable ADD NormalizedPhone AS REPLACE(REPLACE(PhoneColumn, '-', ''), ' ', '') PERSISTED 

This expression removes any spaces and any dashes from PhoneColumn , and these values ​​are stored in a new computed column called NormalizedPhone .

This column will always be updated, even if you change the PhoneColumn value later. It will always contain a normalized phone number automatically.

Now you can easily join the two tables on normalized phone lines, and you should get more accurate results.