CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
DtotheG
DtotheG

Reputation: 1257

Comparing phone numbers sql

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

Currently I'm just doing an Inner join on the tables and only get a few rows returned but would expect more as obviously anything with a hyphen or space will get missed from the join.

The way the system is I can't change the data in the table with 3 formats so can't standardise it or clean it out etc.

What would be the best way to tackle this?

Upvotes: 4

Views: 1461

Answers (1)

marc_s
marc_s

Reputation: 755063

Well you could think about creating a computed column in your 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 the PhoneColumn and those values are stored in a new, computed column called NormalizedPhone.

This column will always be kept up to date - even if you change your PhoneColumn's value later on. It will always contain the normalized phone number automagically.

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

Upvotes: 6

Related Questions