maurizio maurizio - 1 month ago 15
MySQL Question

Sorting numbers string with letter

I have strings such as

70x100 CM
70x140 CM
70x70 CM
72x120 CM
70x130x70 CM
70x75x70 CM
72x72 CM


When I want to sort in this order:

70x70 CM
70x75x70 CM
70x100 CM
70x130x70 CM
70x140 CM
72x72 CM
72x120 CM


this is my code:

ORDER BY CAST(CAST(eaov.value AS DECIMAL(3,0)) as UNSIGNED INTEGER), eaov.value


Any ideas?

Answer

A minor mod to the answer by Strawberry. This is changing the 'cm' to a series of x0 to ensure that there are always 3 dimensions for the sort order

SELECT *
FROM eaov
ORDER BY CAST(SUBSTRING_INDEX(REPLACE(eaov.value, ' CM', 'x0x0x0'), 'x', 1) as UNSIGNED INTEGER),
        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(eaov.value, ' CM', 'x0x0x0'), 'x', 2), 'x', -1) as UNSIGNED INTEGER),
        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(eaov.value, ' CM', 'x0x0x0'), 'x', 2), 'x', -1) as UNSIGNED INTEGER)

Performance will not be its strong point.