**I Am trying to Find Max Invoice **

SELECT IFNULL(MAX(SUBSTRING_INDEX(invoice,'I', -1)) 1,1) AS invoice
FROM sales

SQL Fiddle

**When i run this SQL quarry it can not count more than 10 **

**I Am trying to Find Max Invoice 12 1 = 13 **

CodePudding user response：

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.