Search code examples
mysqlselectprefix

How do I search a prefix in a MySQL table?

I'm given a credit card and i'm trying to determine what type of credit card it is using the first couple of digits of the card.
For example, if the card starts with 4833 then my query should return Visa.
If it starts with 4026 then it would return Visa Electron.

How do I write a query to do this?

My table name is cards

+-----+---------------+--------+
| id  | card_name     | prefix |
+-----+---------------+--------+
| 915 | Visa          |      4 |
| 916 | Visa Electron |   4026 |
| 917 | Visa Electron | 417500 |
| 918 | Visa Electron |   4405 |
| 919 | Visa Electron |   4508 |
| 920 | Visa Electron |   4844 |
| 921 | Visa Electron |   4913 |
| 922 | Visa Electron |   4917 |
+-----+---------------+--------+
Solution

  • A regex will works for your case. The regex you need is simply the pattern followed by a wildcard character.

    However, with simple matching, you will get multiple results, for example 4026 will match both 4 and 4026. In order to fix this, you choose the most restrictive one as the match and the most restrictive one will be the prefix with longest length.

    SELECT card_name 
FROM cards 
WHERE [CREDIT CARD NUMBER] LIKE CONCAT(prefix, '%')
ORDER BY LENGTH(prefix) DESC 
LIMIT 1;