SQL - Query Phonenumber that are stored inconsistently

Question

we a phonenumber field in our database and I would like to do a simple lookup query like:

SELECT * FROM TABLE WHERE Phonenumber = '555123456'

But since the phonenumbers are entered by users and are not normalized, we don't really know what they look like.

Could be:

• +555-123456

or

• (555) 123 456

or

• 555-12-34-56

or something complety different.

The only thing certain is that the all the given numbers should be there in the correct order. Is it possible to construct a query around that?

Answer 1

IF you can alter the table (assuming it's SQL Server 2005 and up), you could add a computed column to your table, and persist it. This column could hold a "cleaned up" representation of your "phonenumber" field.

Something like this:

 create function dbo.CleanPhone(@phone varchar(100))
 returns varchar(100)
 with schemabinding
 as begin
   return
     replace(replace(replace(replace(replace(replace(@phone, ' ', ''), 
             '-', ''), '(', ''), ')', ''), '-', ''), '+', '')
 end

and then:

alter table (yourtable)
 add cleanedPhone as dbo.CleanPhone(Phone) persisted

Now, your "CleanedPhone" column would always contained a "cleaned up" version of your phone number - always something like: 555123456.

Since it's a PERSISTED field, you don't incur a performance penalty when querying, either - the value is created and stored in your table, and is available as a normal column.

On this, you could now query quite easily.

Marc