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

Nick Ryan (NZ) 121 Reputation points
2021-06-23T02:06:02.357+00:00

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 );
Transact-SQL
Transact-SQL
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
    2021-06-23T03:37:29.977+00:00

    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

    0 comments No comments

0 additional answers

Sort by: Most helpful