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

Nick Ryan (NZ) 121 Reputation points

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.

 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 );
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,546 questions
{count} votes

Accepted answer
  1. Tom Cooper 8,466 Reputation points

    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;


    0 comments No comments

0 additional answers

Sort by: Most helpful