SQL - a query that is stored inconsistently

Question

we use the phonenumber field in our database, and I would like to make a simple search query, for example:

SELECT * FROM TABLE WHERE Phonenumber = '555123456'

But since phonenumbers are user-entered and not normalized, we don’t know what they look like.

May be:

• + 555-123456

or

• (555) 123 456

or

• 555-12-34-56

or something completely different.

The only thing that matters is that all the numbers indicated must be in the correct order. Is it possible to build a query around this?

Answer 1

Since I don’t know which RDBMS you are looking for, I will give the most general way:

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

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

Answer 2

Oracle:

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

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'

, ,

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'

, , .

Answer 3

( , SQL Server 2005 ), . "" "phonenumber".

- :

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

:

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

" " "" - - : 555123456.

PERSISTED, , - , .

.

Answer 4

- :

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

.

Answer 5

, , , , . , , . , , , , , . , ( .. ) , , .

Answer 6

@Eric PHP, . (, $_GET )

    $phone                  = $_GET['phone'];
    $phone_formatted        = "%";
    for ($i=0; $i<strlen($phone); $i++) {
        $phone_formatted .= ($phone[$i] . "%");
    }

$phone_formatted, "% x% x% x% x% x% x% x% x% x%", x - .

sql:

phonenumber like $phone_formatted

1231231234, 123-123-1234, [phone] ..

, ( , ) $_GET ['phone'], [phone] .