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?
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).