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 );