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
01234 567890 or
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?
One Solution collect form web for “Comparing phone numbers sql”
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
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.