CODEWITHSUNDEEP
sqlsql-server-2000
Imsopov
Imsopov

Reputation: 155

Longest prefix match in SQL Server 2000

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

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

How would I go about doing this?

e.g. 

records.number = '0841234567'

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

I would want to select 08412

Upvotes: 1

Views: 831

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

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

Update.

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

Upvotes: 4

Related Questions