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 query, 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
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.
