SQL Server 2000 中的最长前缀匹配 [英] Longest prefix match in SQL Server 2000

问题描述

我有两张桌子.让我们只说它的记录和数字

I have 2 tables. lets just say its records and numbers

records.number 包含一个数字，我想在 SQL Server 2000 中的 numbers.prefix 中找到最长可能的关联前缀

the records.number contains a number which I want to find the longest possible associated prefix in numbers.prefix in SQL Server 2000

我该怎么做?

例如

records.number = '0841234567'

numbers.prefix = '084'
                 '0841'
                 '08412'

我想选择 08412

推荐答案

select top 1 N.Prefix
from Numbers as N
  inner join Records as R
    on R.Number like N.Prefix+'%'
order by len(N.Prefix) desc

更新.

Records 中每个数字的最长前缀.

The longest prefix for each number in Records.

select R.Number,
       (select top 1 N.Prefix
        from Numbers as N
        where R.Number like N.Prefix+'%'
        order by len(N.Prefix) desc) as Prefix
from Records as R

SE-Data

这篇关于SQL Server 2000 中的最长前缀匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！