question

NickRyanNZ-4337 avatar image
0 Votes"
NickRyanNZ-4337 asked TomCooper-6989 answered

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

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 );
sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)
along with your expected result? So that we’ll get a right direction and make some test.

0 Votes 0 ·

I tried doing the above changes as a Reply to your request but clicking Submit didn't work when I had a section of Code embedded.

0 Votes 0 ·

1 Answer

TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered

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





5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.