SQL - Query Phonenumber that are stored inconsistently

Question

Hi,

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

In Oracle:

SELECT  *
FROM    mytable
WHERE   REGEXP_REPLACE(Phonenumber, '[^0-9]', '') = '5551234567'

In SQL Server 2005+:

WITH    digits AS
        (
        SELECT  1 AS digit
        UNION ALL
        SELECT  digit + 1
        FROM    digits
        WHERE   digit <= 100
        )
SELECT  *
FROM    mytable
WHERE   (
        SELECT  SUBSTRING(number, digit, 1) AS [text()]
        FROM    digits
        WHERE   SUBSTRING(number, digit, 1) BETWEEN '0' AND '9'
        FOR XML PATH('')
        ) = '5551234567'

, or, if you want to see the normalized phone value,

WITH    digits AS
        (
        SELECT  1 AS digit
        UNION ALL
        SELECT  digit + 1
        FROM    digits
        WHERE   digit <= 100
        ),
        phones AS
        (
        SELECT  m.*,
                (
                SELECT  SUBSTRING(number, digit, 1) AS [text()]
                FROM    digits
                WHERE   SUBSTRING(number, digit, 1) BETWEEN '0' AND '9'
                FOR XML PATH('')
                ) AS nphone
        FROM    mytable m
        )
SELECT  *
FROM    phones
WHERE   nphone = '5551234567'

However, you better create another column for normalized phone values, fill it in a trigger and index it, so you can query more efficiently.

Answer 2

Since I don't know what RDBMS you're looking for, I'll give the most generic way:

phonenumber like '%5%5%5%1%2%3%4%5%6%'

This assumes that all phone numbers are at least equal length (in digits).

Answer 3

You can try something like:

SELECT * FROM TABLE WHERE REPLACE( REPLACE( REPLACE( REPLACE(phone,'(','') ,')','') ,'-','') ,'+','') ,' ','') = '0398765432'

Replace any non numeric value with an empty string.

Answer 4

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

Answer 5

ALternatively, you could fix your data entry pages to strip all nonnumeric characters before entry into the database and then fix all existing data to strip the numbers. Then put a constraint on the field that requires all characters to be numeric. Then you don't need to keep looking for nonnumeric characters every time you want to display a phone number and you can easily have the user interface consistently show them in whatever format you prefer them to be shown in. Where possible, fixing the problem (ie that you didn't put proper controls in your user interface) is better than creating workarounds which will usually create performance slowdowns for no good reason.