I cant sort max query of varchar

Question

I have id table primary key using varchar (BG-1) , I check max of id to decide what next id, but when data reach BG-10, the table cant add more data, because my query read BG-9 as max not BG-10, how to fix it??

Possible dublicate of: https://stackoverflow.com/questions/8557172/mysql-order-by-sorting-alphanumeric-correctly — Stanislav Ivanov, Jan 27 '18 at 03:24

score 0 · Accepted Answer · answered Jan 27 '18 at 03:25

0

Suppose your primary key is a column named "id" you could cast the number part as an integer and eliminate the "BG-" part, like this:

SELECT MAX(CAST(SUBSTR(id,4) AS UNSIGNED)) FROM table

answered Jan 27 '18 at 03:25

JBA

2,889
1
21
38

thank, work perfectly – andruw oei Jan 27 '18 at 03:29

I cant sort max query of varchar

1 Answers1