sql - MySQL sort values that contain two numbers separated by "x" -

i have values consisting of decimal numbers , alphabets:

2mm 3x4mm 2.5x5mm 1.7x6mm 1.7x9mm 1.6x5mm 3mm 5mm 3.5x5mm 3.5x8mm 10x7mm 

note: x - means alphabet x. want sort them ascending result looks like:

1.6x5mm 1.7x6mm 1.7x9mm 2mm 2.5x5mm 3mm 3x4mm 3.5x5mm 3.5x8mm 5mm 10x7mm 

so need both decimal , alphanumerical sorting.

you need use mysql string functions. example, can use locate , substring_index functions separate 2 numbers:

/* create table test (value varchar(100) default null); insert test (value) values     ('2mm'),     ('3x4mm'),     ('2.5x5mm'),     ('1.7x6mm'),     ('1.7x9mm'),     ('1.6x5mm'),     ('3mm'),     ('5mm'),     ('3.5x5mm'),     ('3.5x8mm'),     ('10x7mm'); */  select     value,      1 * case when locate('x', value) > 0 substring_index(value, 'x', 1) else substring_index(value, 'mm', 1) end x,     1 * case when locate('x', value) > 0 substring_index(substring_index(value, 'x', -1), 'mm', 1) else null end y test order x, y 

for illustration have placed calculations inside select clause instead of order clause. result:

+---------+------+------+ | value   | x    | y    | +---------+------+------+ | 1.6x5mm |  1.6 |    5 | | 1.7x6mm |  1.7 |    6 | | 1.7x9mm |  1.7 |    9 | | 2mm     |    2 | null | | 2.5x5mm |  2.5 |    5 | | 3mm     |    3 | null | | 3x4mm   |    3 |    4 | | 3.5x5mm |  3.5 |    5 | | 3.5x8mm |  3.5 |    8 | | 5mm     |    5 | null | | 10x7mm  |   10 |    7 | +---------+------+------+