**I Am trying to Find Max Invoice **
SELECT IFNULL(MAX(SUBSTRING_INDEX(invoice,'I', -1))+1,1) AS invoice
FROM sales
**When i run this SQL quarry it can not count more than 10 **
| invoice |
|---|
| 20221026P1I1 |
| 20221026P1I2 |
| 20221026P1I3 |
| 20221026P1I4 |
| 20221026P1I5 |
| 20221026P1I6 |
| 20221026P1I7 |
| 20221026P1I8 |
| 20221026P1I9 |
| 20221026P1I10 |
| 20221026P1I11 |
| 20221026P1I12 |
**I Am trying to Find Max Invoice 12 + 1 = 13 **
>Solution :
Your use of SUBSTRING_INDEX() is correct, however you should cast the string value to a bona fide integer:
SELECT COALESCE(MAX(CAST(SUBSTRING_INDEX(invoice, 'I', -1) AS UNSIGNED)), 1) AS invoice
FROM sales;
The problem with trying to find the max of the text substrings themselves is that text numbers sort lexicographically, e.g.
1
10
11
2
23
But this isn’t the behavior you want, you want the numeric maximum. Hence we should cast these substrings and then compare.
Side note: You could have avoided this problem entirely by maintaining a pure numeric invoice number column. You may want to change your table design to include such a column.