mysql custom order by with mixed data types

Question

In the following mysql query I'm using a custom order by statement so I can display various sizes in a specific order instead of alphabetical:

select distinct size 
from product p left join productsizes ps 
             on p.productcode = ps.size_prodcode 
order by field(size, 'XXS', 'XS', 'S', 'M', 'L', 'XL', 'XXL', 'XXXL')

In cases where some products also have numeric sizes, how do I write the order by so that it places the numeric sizes in an ascending order along with the custom order?

An example of the desired output:

30, 32, 34, S, M, L

or

S, M, L, 30, 32, 34

Answer 1

FIELD() returns 0 when the search string is not found. Therefore:

ORDER BY FIELD(size, 'XXS', 'XS', 'S', 'M', 'L', 'XL', 'XXL', 'XXXL'), size

Answer 2

try

ORDER BY (CASE WHEN sizes REGEXP '(\d)+' THEN 0 ELSE 1 END) ASC,
         field(sizes, 'XXS', 'XS', 'S', 'M', 'L', 'XL', 'XXL', 'XXXL')

Answer 3

You should try use an enum field with all your sizes in it. It will maintain its sort order.

CREATE TABLE ... (
  ...
  size ENUM ('30', '32', '34', 'S', 'M', 'L')
  ...
);

Or

CREATE TABLE ... (
  ...
  size ENUM ('S', 'M', 'L', '30', '32', '34')
  ...
);