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 Ser

Solution 1:

select top 1 N.Prefix
from Numbers as N
  inner join Records as R
    on R.Number like N.Prefix+'%'orderby 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+'%'orderby len(N.Prefix) desc) as Prefix
from Records as R

SE-Data