Card bin query sql sharing
Card bin: the first six digits of the card number represent the card issuing bank identification code, also known as the BIN number. Different BIN numbers represent different bank card organizations and card levels.
Recently updated the card bin table data (bank_card_bin) of the system, found that the card bin length is not the above 6 digits, the card bin length is 8 digits (some 6 bits, some 7 bits, non-fixed length), as shown in the following table
Branch name card type card binka bin length organization code Wulat Central Banner Mengyin Village Town Bank Wulat Central Banner Mengyin debit Card 6213460781915142075 Mengyin debit Card 6213460781915142075 Mengyin debit Card 6213462681915142073 Mengyin debit Card 6213462481915142054 Tiemeng Yincun Town Bank, Ordos City Debit card 6213461181915142051 Sizi Wang Mengyin Village Bank Bank Mengyin debit Card 6213461381915142046
So how to query the specific card information according to the bank card number?
The initial idea is to capture the first six digits of the screenshot card number and limit the length of the card to query. If there is not only one returned result, continue to intercept the first 7 digits for query until only one returns the result. If there are 2 or more results, but there are 0 records in the further query, it is considered that the bin data of the card is not included. That is, recursive query. Suppose 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 troublesome. How is it possible? In the communication and confirmation of colleagues, some changes can be made, as follows
Select * from bank_card_binwhere '6213462465616156' like concat (card bin,'%') and length =' 19'
Cleverly split the previous sql into two segments, and the card bin is not intercepted by the user's own judgment, but a match.
At the same time, this sql takes advantage of the feature that "card bin is unique", and there is no inclusion relationship, that is, there is no record of the following
* * Bank * Bank Mongolian Bank debit Card 6213466191514204
