CODEWITHSUNDEEP
mysqlsql-order-by
silkfire
silkfire

Reputation: 25945

ORDER BY two variable parts of a string

I'm stuck and don't know how to proceed further. How do I order my results accordingly?

10 x  2 ml
10 x 10 ml
 4 x 20 ml

10 x 2 ml should come first because 2 ml is smaller than 10 ml.
And then order by the number that comes before the multiplication sign.

Upvotes: 0

Views: 48

Answers (2)

silkfire
silkfire

Reputation: 25945

This is how I solved my own question:

ORDER BY SUBSTR(size, INSTR(size, 'x') + 2) + 0, size + 0

Upvotes: 1

aconrad
aconrad

Reputation: 586

You could try this, but it's really ugly, especially if the tables are big and you need performance:

ORDER BY  TRIM(REPLACE(REPLACE(field_name,CONCAT(SUBSTRING_INDEX(field_name,'x',1),'x'),''),'ml',''))

it replaces SUBSTRING_INDEX('ABCx123ml','x',1); //ABC and ml with blanks, triming it, leaving only the value needed for order...

Upvotes: 0

Related Questions