Match a value to a lookup table with a key of varying length

Question

Sorry but this isn't easy to describe.

I'd like to find the Product for each card transaction. I want to search for the BINNumber that has the most digits matching the card number. In the sample data below, the transaction 1 would return product 1, transaction 2 would return product 2 and transaction 3 would return product 3.

CREATE TABLE LookupCardType
 (
 Scheme varchar(10) NOT NULL,
 ProductName varchar(100) NOT NULL,
 BINNumber varchar(19) NOT NULL
 );

INSERT INTO LookupCardType( Scheme, ProductName, BINNumber )
VALUES( 'Mastercard', 'MasterCard Business to Business Credit', '6229340145' ),
   ( 'Mastercard', 'MasterCard Gold Debit', '622934' ),
   ( 'Mastercard', 'MasterCard Platinum Debit', '622' );

CREATE TABLE CardTransactions
 (
 TransactionTime datetime NOT NULL,
 CardNumber varchar(19) NOT NULL,
 TransactionAmount decimal(10,2) NOT NULL
 );

INSERT INTO CardTransactions( TransactionTime, CardNumber, TransactionAmount )
VALUES( '2021-06-22 07:03:00.123', '6229340145630001', 23.99 ),
   ( '2021-06-22 07:13:44.023', '6229340045630002', 5.99 ),
   ( '2021-06-22 09:22:00.444', '6229330045630003', 275 );

Answer 1

Thanks for the sample tables and data. It would help if you also gave us what you wanted for the result. But I think you are looking for

;With cte As
(Select lct.Scheme, lct.ProductName, lct.BINNumber, 
   ct.TransactionTime, ct.CardNumber, ct.TransactionAmount, 
   Row_Number() Over(Partition By ct.CardNumber Order By Len(lct.BINNumber) Desc) As rn
From LookupCardType lct
Inner Join CardTransactions ct On CharIndex(lct.BINNumber, ct.CardNumber) > 0)
Select Scheme, ProductName, BINNumber, TransactionTime, CardNumber, TransactionAmount
From cte
Where rn = 1;

Tom