Sort hypen separated value in mysql varcharfield -

Sort hypen separated value in mysql varcharfield -

how sort hyphen separated number in mysql varchar field. have list of number in field

700-657-1 700-657-10 700-657-2 700-657-3 700-657-4

if always have three-part (fixed count or can determine maximum count of parts) values, have 2 choices:

consider rethink schema store info in separate columns (the exact parts should have meaning) parse string string functions, sort parsed values

an illustration how sec option:

select val, cast(replace(substring(substring_index(val, '-', 1), length(substring_index(val, '-', 0)) + 1), '-', '') unsigned ) first, cast(replace(substring(substring_index(val, '-', 2), length(substring_index(val, '-', 1)) + 1), '-', '') unsigned ) second, cast(replace(substring(substring_index(val, '-', 3), length(substring_index(val, '-', 2)) + 1), '-', '') unsigned ) 3rd test order first asc, sec asc, 3rd asc

you can move look order by clause if not want homecoming them.

sql fiddle demo

edit

select val test order cast(replace(substring(substring_index(val, '-', 1), length(substring_index(val, '-', 0)) + 1), '-', '') unsigned ) asc, cast(replace(substring(substring_index(val, '-', 2), length(substring_index(val, '-', 1)) + 1), '-', '') unsigned ) asc, cast(replace(substring(substring_index(val, '-', 3), length(substring_index(val, '-', 2)) + 1), '-', '') unsigned ) asc

mysql