Card bin query SQL share

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.