SQL-查询存储不一致的电话号码

因为我不知道你在找什么RDBMS,我将给出最通用的方法:

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

这假设所有电话号码的长度至少相等(以数字表示)。

如果你能改变表格(假设它是 SQL Server 2005 向上),您可以向表中添加一个计算列,并将其持久化。此列可以保存“已清理”的“电话号码”字段表示。

像这样:

 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

现在,您的“cleaned phone”列将始终包含您的电话号码的“cleaned up”版本-始终类似于:555123456。

因为它是一个持久化字段,所以在查询时也不会受到性能惩罚——该值是创建并存储在表中的,并且可以作为普通列使用。

在这个问题上,您现在可以很容易地查询了。

马克

在 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'

但是,最好为规范化的电话值创建另一列,将其填充到触发器中并编制索引,以便更有效地查询。

您可以尝试如下操作:

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

用空字符串替换任何非数字值。

或者,您可以修复数据输入页,以便在进入数据库之前删除所有非数字字符,然后修复所有现有数据以删除数字。然后对要求所有字符都是数字的字段进行约束。然后,您不必每次想显示电话号码时都一直寻找非数字字符,您可以轻松地让用户界面以您喜欢的任何格式一致地显示它们。在可能的情况下,解决问题(即您没有在用户界面中放置适当的控件)比创建解决方法要好,后者通常会毫无理由地导致性能下降。

我采用了@eric的概念,并使用了一些PHP代码,这将适用于任何电话号码输入。(假设$\u get有电话号码输入)

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

这将导致$phone_格式的值看起来像“%x%x%x%x%x%x%x%x%x%x%”,其中x是任意数字。

现在可以使用SQL查询:

phonenumber like $phone_formatted

如果输入1231231234,它将匹配123-123-1234,(123)123-1234等变体。

如果您想更进一步,您还可以从$ou get['phone']值中删除所有非数字字符(删除括号、短划线和空格),这样(123)123-1234之类的输入也将匹配上述所有情况。