CODEWITHSUNDEEP
mysqlsql
Shafayet Nur Alam
Shafayet Nur Alam

Reputation: 21

SUBSTRING_INDEX Not Warking in Mysql

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 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

Upvotes: 0

Views: 507

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

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.

Upvotes: 1

Related Questions