[Solved] MySQL order by a column which has multiple numerical values separated by slashes

by


Far and away the best solution is to redesign your schema to store each part of notenumber in a different field. Barring this, you need to split out each part of notenumber, convert it to a numeric type, and order by it. You’ll have to add a clause to the order by for each part you split out, there’s no way around that.

Example:

select
  notenumber,
  replace(substring(substring_index(notenumber, "https://stackoverflow.com/", 1), length(substring_index(notenumber, "https://stackoverflow.com/", 1 - 1)) + 1), "https://stackoverflow.com/", ''),
  replace(substring(substring_index(notenumber, "https://stackoverflow.com/", 2), length(substring_index(notenumber, "https://stackoverflow.com/", 2 - 1)) + 1), "https://stackoverflow.com/", ''),
  replace(substring(substring_index(notenumber, "https://stackoverflow.com/", 3), length(substring_index(notenumber, "https://stackoverflow.com/", 3 - 1)) + 1), "https://stackoverflow.com/", '')
from test
order by
  convert(replace(substring(substring_index(notenumber, "https://stackoverflow.com/", 1), length(substring_index(notenumber, "https://stackoverflow.com/", 1 - 1)) + 1), "https://stackoverflow.com/", ''), signed integer),
  convert(replace(substring(substring_index(notenumber, "https://stackoverflow.com/", 2), length(substring_index(notenumber, "https://stackoverflow.com/", 2 - 1)) + 1), "https://stackoverflow.com/", ''), signed integer),
  convert(replace(substring(substring_index(notenumber, "https://stackoverflow.com/", 3), length(substring_index(notenumber, "https://stackoverflow.com/", 3 - 1)) + 1), "https://stackoverflow.com/", ''), signed integer)
;

Note you’ll need to insert the index number of the split part twice into each replace clause.

SQL fiddle for the above solution: http://sqlfiddle.com/#!9/dc935/1/0

0

solved MySQL order by a column which has multiple numerical values separated by slashes