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.

  • SQL Server: How to get a subitem of sp_helplanguage?
  • use a variable in the table name when renaming a table in SQL
  • using a SqlConnection in C++ / .NET
  • SQL query that finds repeated data in one column with different data in a second column
  • SSRS 2005 find name of column with max value
  • Sort Bullets in Database
  • 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 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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.