查找电话号码 - 查找带或不带分机号码的号码

Question

我有一个包含大约130,000条电话号码记录的表格。这些号码都是按照这种格式 [phone] 格式化的。这些号码始终包括国际区号、本地区号，然后是电话号码，有时还包括分机号。

有一个网络服务检查来电者的号码是否在表中。该服务已经可以使用。但现在客户还希望，即使有人从已经在数据库中但不是他的分机号的公司打电话，该服务也会返回一些结果。

以下是表格的示例。

id | telephonenumber | name -- | ---------------- | ------ 1 | [phone] | 公司A 2 | [phone] | 公司A的员工 3 | [phone] | 公司B

现在，如果来自公司A的某个人以不同的分机号（例如+43123456777）打电话，则应返回id1。但问题是，我不知道分机号有多少位数。它可能有3、4或更多位数字。

是否有任何字符串匹配的模式？

数据存储在sql2005数据库中。

谢谢

编辑：

我从CRM系统中获取电话号码。我已经与CRM管理员交谈，他正在尝试以不同的格式向我发送数据。

id | telephonenumber | extension | name -- | ---------------- | --------- | ------ 1 | [phone] | | 公司A 2 | [phone] | 890 | 公司A的员工 3 | [phone] | | 公司B

- nWorx

Answer 1

有没有办法确定存储的号码中哪一部分是分机号？或者说没有分机号的“基本”号码被存储了。如果是这样，你可以检查数据库中的一个没有分机号的号码是否是当前号码的前缀来进行检查。前缀是指从字符串开头开始的子字符串。

但是，如果你的数据库中只有带有分机号的号码，而且无法找出其中有多少位数字属于分机号，我认为你将无法找到精确的解决方案。

- HansDampf

Answer 2

与其在数据库中查找电话号码，你可以反转问题并检查数据库中的每个号码，以查看它是否与来电号码匹配或前缀匹配。

假设你从呼叫者ID获得一个电话号码，例如+431234567891，则

SELECT name, id
FROM Table
WHERE CHARINDEX(telephonenumber, "[phone]") > 0;

如果输入的是公司名称，那么返回该公司所有记录；如果输入的是+431234567890，则返回2条记录。

• 公司名称
• 实际分机号码

如果你能够处理客户端返回的两行数据，那么以上内容应该不成问题。

对数据进行预处理可以提高性能，但这需要更详细地描述数据，例如：

• 分机号码只有3位或4位吗？
• 基础号码始终为9位或10位吗？
• 是否每个有分机的公司都至少有一个分机号码？等等。

- Unreason

---

扩展号码长度因公司而异，同时基础号码也取决于国家和是否为特殊号码或普通电话号码。 - nWorx

好的，由于您的数据将变得更好，您是否仍需要编写查询的帮助？另外，您是否尝试过我的原始建议-它应该已经足够了（它也可以与新结构一起使用）。 - Unreason

+1 现在我明白你在做什么了 - 你反转了问题。你不是在数据库中寻找电话号码，而是检查数据库中的每个号码，看它是否与传入号码匹配或作为前缀存在。非常好。我不确定这是否比我的二分查找更有效，但肯定更优雅。 - Mark Booth

Answer 3

考虑到不同公司的分机号码位数不同，不同国家和地区的电话号码位数也可能不同，这是一个需要高效解决的棘手问题。即使你将数据表拆分为基础号码和分机号码，你仍然需要将来电号码拆分为基础号码和分机号码，我认为这实际上会让事情变得更加复杂。

我倾向于尝试以下方法:

原始格式

1. 尝试将来电号码与数据库匹配。 - 如果它匹配一个记录，那么你就可以得到答案 - 特定人员。 - 如果它匹配多个记录，那么出了问题，所以失败。 - 否则，你必须找到公司: 2. 去掉来电号码末尾的一位数字，再次尝试将其与数据库匹配。 - 如果数字位数低于阈值（可能是6位数字），那么你的搜索应该失败。这只是为了在不会找到号码时限制执行的数据库搜索数量。 - 如果它没有匹配任何记录，则需要再次尝试此步骤。 - 如果它匹配多个记录，那么出了问题，所以失败。 - 如果它恰好匹配一个记录，那么就可以得到下一个最佳答案 - 公司。

例如，在查找“[phone]”时:

- [phone]不匹配任何记录。 - [phone]不匹配任何记录。 - [phone]匹配1个记录：“公司A”

这种方法的主要故障模式是如果公司的分机号码长度可变。例如，考虑如果431234567890和43123456789都是有效的电话号码，但只有第二个号码在数据库中。如果来电号码是431234567890，则会误匹配43123456789。

拆分格式

这更加复杂，但更健壮。

尝试将传入号码与数据库匹配：

- 如果它与一条记录匹配，则您有答案-公司。 - 如果它与多个记录匹配，请匹配没有分机的条目，然后您就找到了公司。 - 否则，您必须找到基础公司号码和分机号码：

从传入号码中去掉末尾数字，然后再次尝试将其与数据库匹配。

- 如果数字数量低于阈值（可能是6位数字），则您的搜索可能会失败。这只是为了在不会找到号码时限制执行的数据库搜索次数。 - 如果没有匹配记录，则需要再次尝试此步骤。 - 如果它与一条记录匹配，则您已经找到了答案-公司。 - 如果它与多个记录匹配，则您已经找到了公司的基础号码，因此现在知道了分机号码，因此可以尝试查找特定人员：

从原始传入号码的开头剥离基础号码，并使用此号码搜索具有该基础号码的记录的扩展名。

- 如果它与一条记录完全匹配，则您已经找到了一个特定的人员。 - 如果它没有匹配特定的人员，则匹配没有分机的条目，然后您就找到了公司。

例如，在搜索“+ 43123456777”时：

- [phone]与0个条目匹配。 - [phone]与0个条目匹配。 - [phone]与2个条目匹配：“empty：Company A”和“890：employee in company A”。 - 在这两个匹配项中，“77”没有匹配项，因此返回空扩展名：“Company A”。

实现注意事项：

如上所述，此算法确实存在一些效率问题。如果数据库查找很昂贵，则它具有与电话号码长度相关的线性成本，特别是在数据库中不存在类似的号码的情况下（例如，如果传入号码来自哈萨克斯坦，但数据库中没有哈萨克斯坦号码）* 8'）。

不过，您可以相对容易地添加一些优化。如果您处理的大多数公司使用3或4位数字分机，您可以从末尾剥离4位数字，然后进行二分查找，直到达到答案。这将在许多情况下将15位数字减少到4或5位，并最多进行6次查找。

此外，每次缩小选择范围时，您都可以仅在先前选择范围内进行选择，而无需在整个数据库中进行选择。

终于弄清楚了Unreason的答案如何工作，我发现这是一个更简单、更优雅的解决方案。我希望我能想到在传入号码中查找数据库号码而不是反过来。

我的唯一担忧是，在数据库中对每个telephonenumber执行此操作可能会对服务器造成过多的负担。我建议在最大压力下对该解决方案进行基准测试，看它是否会引起问题。如果没有问题，那就好 - 使用它。如果有问题，考虑实施我的算法的简单形式，并再次进行压力测试。如果性能仍然太低，可以尝试使用我的二分搜索建议。

- Mark Booth

---

我在我的回复中添加了一个示例，以展示为什么（在我看来）仍然无法解决这个问题的原始形式（即缺少进一步信息）。 - MartinStettner

将每个具有相同前x位数字的数字放在同一组中，即使它们不属于同一公司。 - HansDampf

@MartinStettner，我认为你或者是我误解了问题。我不明白为什么我的建议算法不能解决nWorx的问题。 - Mark Booth

@HansDampf，从问题中可以得出的含义是，公司基础号码将始终是原始格式中基础号码及其扩展的子集。在拆分格式中，基础号码将始终是明确的，因此[phone]永远不会意外匹配Company A，即使它被缩短为[phone]，也不会匹配[phone]。 - Mark Booth

Answer 4

扩展号码中的数字是特定于PBX的。 地区代码+电话号码中的数字是特定于国家/运营商的。

一个方法是定义其他规则，例如...

+43123 | 12

... 表示以 +43123 开头的任何内容都是12位数字，并且超出此范围的任何内容都是扩展：这使您可以使用（可配置而不是硬编码的）数据来指定扩展从哪里开始。

另一种方法可能是要求任何带扩展号码的条目都应有相应的不带扩展号码，就像你所展示的“公司A”的例子。

- ChrisW

---

+1，但需要访问这些号码。可能很困难（我知道至少对于德国来说这将是可怕的，因为有效号码范围从3（罕见）到每个区号7或可能8，具体取决于地区）。 - Benjamin Podszun

是的，那是个好主意，但问题在于还有一些特殊号码，它们比普通电话号码短。 - nWorx

“特殊”数字不可能看起来像另一个区号吗？对于更复杂的情况，您可以指定匹配字符串为正则表达式，以便您可以指定诸如“+43 [2-8] 23”之类的内容。 - ChrisW

只是不像维护同样的代码那样糟糕；它可以将问题/维护推给客户（或技术支持或销售人员）。 - ChrisW

Answer 5

我的理解是，电话号码系统中不存在一个有效/完整的号码是另一个号码的前缀。在这里，常见的恶作剧是将你的号码告诉别人是11 05 32或类似的，其中110是德国紧急警察电话号码。

因此，如果您可以更改数据库结构并预处理数据，您可以查找具有相同前缀的号码（首先按顺序排列，如果较长的号码以最短的号码开头，则它们是扩展）。每个匹配项都是

• 基础号码（最短的号码）
• 直接号码加分机号码（所有更长的号码）

如果可能的话，我会在数据库中标记这些内容以进行更快速的查找。

这种方法对于您拥有共同默认分机的情况不适用。在这里，许多公司提供类似于1234567-0的外部号码，其中0可以替换为2-4位数字的分机号码。对于这些情况，我的方法会失效 - 对于您的示例数据，它应该可以工作吧？

- Benjamin Podszun

---

数字格式始终正确，号码匹配来自呼叫者ID，因此不会伪造。您的方法应该可行。 - Unreason

Answer 6

如果你在处理来自不同国家的电话号码，这几乎是不可能的。长度通常会发生变化，即使在同一个国家内也是如此。如果你知道长度将是什么（或者你想像ChrisW一样维护一个列表），你可以使用LEFT(field, x)函数在搜索公司电话号码之前截断电话号码。请注意，如果你正在执行连接操作，它可能会运行得更慢，因为它必须对每一行运行该函数。

- Nelson Rothermel

---

不仅如此，在英国，如果我没记错的话，你可以购买整个直拨号码范围，并只使用所有号码中共同的数字，即如果你有0123 456700到0123 456799，你可以使用0123 4567作为你呈现的来电者ID。此外，这个呈现的ID可以独立于该号码是否是一个有效的接受呼叫的号码。我肯定曾经接到过直销商的电话，他们的来电者ID不是一个有效的回拨号码。 - Mark Booth

我不知道英国是怎么样的，但在美国，来电显示仅被视为“方便”。只要拥有正确的电话公司访问权限，您可以呈现任何您想要的号码。如果您阻止了来电显示，它仍会通过运营商发送，并带有一个标志，在理论上，最后一个运营商将不会向最终接收者（如家庭用户）显示它。我认为有一些新法律防止您隐藏真正的来源，但没有技术限制。 - Nelson Rothermel

Answer 7

如果您的表格结构如上所述，没有更多信息是不可能的。系统无法知道哪个部分是基础号码，哪个是扩展号码。因此，对于任何以“+439”开头的（未知）号码，它都会返回“公司b”。

编辑 (@MarkBooth)

我坚持认为没有额外的信息是不可能的。只是为了让它更清楚：假设我们在数据库中有以下信息

...
[phone] - ....
[phone] - Company A (reception)
[phone] - Company A, Mr. X
[phone] - ....
...

这些号码的结构是+43（316）873-1，但程序并不知道。因此，如果一个具有结构+43316872133（[phone]）的号码在打电话（不在数据库中），你（以及你的软件：）没有更多的信息时，不能确定它是否属于公司A。

唯一的解决方案是维护公司的“基础号码”，对其进行简单的前缀搜索。

- MartinStettner

---

你是否坚持认为这是不可能的？我很想知道我的解决方案中是否存在算法缺陷（而非效率缺陷）。 - Mark Booth

结构完全无关紧要。就像您拨号时不键入结构一样，您在数据库中剥离它。您编辑的数据库不遵循OP给出的示例，应该有一个[phone]-公司A，在这种情况下，我的算法将返回公司A和分机23，以供[phone]使用。 如果您不知道传入号码的基本部分是什么，即使知道每个公司的基本数字也无法帮助您查找它们，这就是我的搜索算法解决的问题。 如果您能够指出该算法的错误，请指出。 - Mark Booth

我认为问题出在你只有某些公司的“分机”号码上：以我的例子来说，这将是+433168731号码：公司A的所有号码都以+43316873开头，但是这个“基础”号码不在数据库中。我认为这是电话号码数据（特别是手动输入的）的常见情况。在这种情况下，我必须坚持我的说法，即每个可能的算法都会错误地报告任何以+4331687...开头的号码属于A公司，因为没有人能够知道什么是基础号码，什么是分机号码。 - MartinStettner

当然，如果您可以保证目录中每个公司的数据始终具有基本数字，则有几种解决问题的方法。另请注意，我所说的与HansDampf的回答第二种情况更或多或少相同。也许他比我表达得更好。 - MartinStettner