I have two tables:
(SName) (SId) Apple1 100 Apple2 200 Banks 300 BanksB 400 Bankerly 500
(PName) (PId) App 1 Bank 2 Banker 3
My goal: To find the longest prefix that matches each of sources. The results would look like this:
(SName) (SId) (PName) (PId) Apple1 100 App 1 Apple2 200 App 1 Banks 300 Bank 2 BanksB 400 Bank 2 Bankerly 500 Banker 3
Constraints: I am using SQL Server 2000 and cannot upgrade. I know how to solve this problem using analytic functions, but they are not available in SQL Server 2000, as far as I know.