MySQL按行中数组的第一个元素排序

Question

I have problem with ordering of my table by row with prices array. Below example rows:

1) ;100
2) ;50
3) ;50;100;300
4) ;30;150

I want to order by first element of array. When I order by price row ASC I get:

1) ;100
2) ;30;150
3) ;50
4) ;50;100;300

It is wrong result because "100" should be last... I tried:

ORDER BY CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(`price`, ';', 2), ';', 1) AS INT(3) ASC

But still wrong... Any clue?

Answer 1

You are very close. If I were doing this I would use (http://sqlfiddle.com/#!2/f57af/6/0)

 ORDER BY 0+TRIM(LEADING ';' FROM price)

This works by getting rid of any semicolon, then (via the 0+ trick) treating the initial characters of the price column as an integer, not as text.

You could also do

ORDER BY 0+SUBSTRING_INDEX(SUBSTRING_INDEX(val,';',2), ';', -1)

or

ORDER BY CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(val,';',2), ';', -1) AS INTEGER)

You had two problems. First, your outer SUBSTRING_INDEX operation needed a third parameter of -1. Second, you cast as INTEGER, not INT(3).