Chayan Chayan - 2 months ago 5
SQL Question

mysql query: search in string position

We have a table which contains

card_no
information. containing data like:

-----------------------------------------
| id [int(11)] | card_no [varchar(16)] |
-----------------------------------------
| 1 | 0124578965874563 |
| 2 | 1245789658478596 |
| 3 | 8471452369587458 |
-----------------------------------------


Now we need a query to find card number(s) which contains 7 in 6th position. Or which contains 4 in 2nd position.

This is actually needed when we printed card numbers and find some numbers unreadable. so we need to identify the card with rest of the numbers. For example we have data like:

1245_896584_8596


Now we need to identify the card with this data.
Thanks in advance.

Answer

You can use function SUBSTRING:

SELECT id, card_no
FROM mytable
WHERE SUBSTRING(card_no, 6, 1) = '7' OR SUBSTRING(card_no, 2, 1) = '4'

Demo here