Card bin query SQL share

Card bin: The first 6 digits of the card number represent the issuer identification code, also called the bin number, and the different bin numbers represent different bank card organization and card levels.

Recently updated the System Card bin table data (bank_card_bin), found that the card bin length is not the aforementioned 6 digits, the card bin length is 8 bits (some 6 bits, some 7 bits, not fixed length), such as the following table,

branch		card name	card type	Card bin	Card bin length	length	Organization code
Wulatezhqi Silver Village Bank	Wulatezhqi Silver Village Bank	Silver debit card	debit card	62134607	8	19	15142075
Dengkou Silver Village Bank	Dengkou village Bank	Silver debit card	debit card	62134626	8	19	15142073
Otog Front Banner Silver Village Bank	Otog Front Banner Village Bank	Silver debit card	debit card		8	19	15142054
Eerduosi Tiexi Village Bank	Eerduosi Tiexi Village Bank	Silver debit card	debit card	62134611	8	19	15142051
Four sons Wang Mongban village Bank	Four sons Wang Mongban village Bank	Mongolian Bank Debit Card	Debit card	62134613	8	19	15142046

So how to query the specific card information according to the bank card number?

The first idea is the first 6 digits of the card number, and limit the length of the card to query, if the return result is not only one, continue to intercept the first 7 to query, until only one return results. If there are 2 or more results, there are 0 records to continue the enquiry, then the bin data of the card is not included. That is, recursive queries. Assuming the card number is 6213462465616156, refer to SQL as follows

SELECT * from Bank_card_binwhere ' 6213462465616156 ' like ' 621346% ' and length = ' 19 '

But it's too much trouble, how could it be? Communicate with colleagues in the confirmation, can make some changes, as follows

SELECT * from Bank_card_binwhere ' 6213462465616156 ' like concat (Card bin, '% ') and length = ' 19 '

Cleverly the previous SQL was split into two paragraphs, and the card bin is not the user's own judgment interception, is to do a match.

At the same time, this SQL takes advantage of the "card bin is unique" attribute, there is no inclusion relationship, that is, the following records do not exist,

* * * Bank

Silver debit card

debit card

621346

6

19

1514204

Card bin query SQL share