postgreSQL:删除VARCHAR中的最后一个字符，直到找到匹配

Question

我正在寻找一种有效的方法来查询postgreSQL数据库，通过删除字符串中最右边的字符，直到找到匹配。例如，如果我的拨号号码是442079285200，那么它应该从序列的末尾剥离字符，最终匹配到UNITED KINGDOM-LONDON44207。

1. 442079285200->没有匹配
2. 44207928520->没有匹配
3. 4420792852->没有匹配
4. 442079285->没有匹配
5. 44207928->没有匹配
6. 4420792->没有匹配
7. 442079->没有匹配
8. 44207->英国-伦敦

v_destination_rates

rounding_rule1-1-11-1-11-1-11-1-11-1-1

您没有说明dialing_number是否来自表/经过处理的用户输入/其他内容。

为简单起见，我假设它来自表contacts，并且您希望返回contacts中的所有内容和v_destination_rates中的每个列，如您所描述的那样连接。

不用pl/pgSQL:

SELECT
*
FROM contacts c
LEFT JOIN v_destination_rates vdr
ON vdr.dialing_code::TEXT LIKE c.dialing_number::TEXT || '%'

我已经在一个包含9,000条记录的表上测试了这一点，我认为它与查找表v_destination_rates一样大或更大，并且在不到十分之一秒的时间内匹配了16个样本整数。

如果拨号代码已经是类型TEXT，并且按字典顺序索引，您可能会获得更好的性能，因为这就是您在这里搜索的方式。

我通常尽可能避免正则表达式和类似/类似的搜索;他们行动缓慢。在这种情况下，它们是完全可以避免的，相反，您可以使用substring和length并进行相等的匹配。(是的，2个子程序vs正则表达式，这是一个折腾)。下面的代码就是这样做的，当出现多个匹配时，它选择最长的匹配。(见演示)

with dialing_number (dn) as 
( values ('442079285200') ) 
select dr.* 
from dialing_number
join v_destination_rates dr
on dr.dialing_code = substring(dn,1, length(dr.dialing_code)) 
order by length(dr.dialing_code) desc
limit 1 ;   

从性能方面考虑，一个20000的搜索集所要搜索的条目数量非常少。出于好奇，我生成了随机的dialing_code值，直到上面的查询花费超过1秒。在1.07秒内搜索了4755006行。

我想知道是否有更有效的方法来执行此查询

可以，解析该号码以获得国家和拨号代码。有很多现有的库可以做到这一点。然后将它们连接起来并搜索。

例如:442079285200为国家代码44，拨号号码20(207已废弃)。然后搜索"4420"。

注:870、882、883不是拨号号，是国家代码。铱是881。把国家代码和拨号代码混在一起可能会导致更多的问题，你最好在你的表中把它们分开。