MySQL Natural ordering of product names

Question

I have a table with the following in the title column

a short name
z short name
Oase AquaMax ECO Premium 10000 Pump
Oase AquaMax ECO Premium 12000 Pump
Oase AquaMax ECO Premium 16000 Pump
Oase AquaMax ECO Premium 4000 Pump
Oase AquaMax ECO Premium 6000 Pump
Oase AquaMax ECO Premium 8000 Pump

And I need to order these as you would expect to see them, As below

a short name
Oase AquaMax ECO Premium 4000 Pump
Oase AquaMax ECO Premium 6000 Pump
Oase AquaMax ECO Premium 8000 Pump
Oase AquaMax ECO Premium 10000 Pump
Oase AquaMax ECO Premium 12000 Pump
Oase AquaMax ECO Premium 16000 Pump
z short name

but when I do:

select `title` from `products` order by `title` ASC

they appear in this order.

a short name
Oase AquaMax ECO Premium 10000 Pump
Oase AquaMax ECO Premium 12000 Pump
Oase AquaMax ECO Premium 16000 Pump
Oase AquaMax ECO Premium 4000 Pump
Oase AquaMax ECO Premium 6000 Pump
Oase AquaMax ECO Premium 8000 Pump
z short name

I have also tried:

select `title` from `products` order by `title` + 0 ASC

but again this just does the same thing, How is the best way to overcome this so that they appear in the correct order?

Jens · Answer 1 · 2015-01-22T11:38:25.780

0

Try

select title from products order by length(title),title ASC

and do not use single quotes arround table and field names.

edited Jan 22 '15 at 11:38

answered Jan 22 '15 at 11:31

Jens

67,715
15
98
113

This work fine untill i add more product in that have a shorter title. I will ajust my question slightly – Steve Taylor Jan 22 '15 at 12:40

score 0 · Answer 2 · answered Jan 22 '15 at 12:03

0

You can use this query to get your title column into ASC order :

SELECT title
FROM temp
ORDER BY CONVERT( SUBSTRING( title, 26, 9 ) , UNSIGNEDINTEGER )

answered Jan 22 '15 at 12:03

Ghanshyam Dekavadiya

153
6

Thats fine if the string is always the same length. – Steve Taylor Jan 22 '15 at 12:43
you mean string is not fixed ? – Ghanshyam Dekavadiya Jan 22 '15 at 13:14

score 0 · Answer 3 · edited May 23 '17 at 12:23

So the main problem is that you have an integer in your string and you would like to use it as a secondary sorting in your query.

Here, the problem is that MySQL does not simply support you in just extracting that number from the string (a regexp would be nice here, as discussed under this question). Another solution might be, to try to find the position of the first number (with locate) as it is shown here.

The following query works if there is at most one number in the title:

SELECT s FROM
  -- this subselect returns positions for the first number in the title
  (SELECT *, LEAST( 
    if (LOCATE('0',title)>0,LOCATE('0',title),999),
    if (LOCATE('1',title)>0,LOCATE('1',title),999),
    if (LOCATE('2',title)>0,LOCATE('2',title),999),
    if (LOCATE('3',title)>0,LOCATE('3',title),999),
    if (LOCATE('4',title)>0,LOCATE('4',title),999),
    if (LOCATE('5',title)>0,LOCATE('5',title),999),
    if (LOCATE('6',title)>0,LOCATE('6',title),999),
    if (LOCATE('7',title)>0,LOCATE('7',title),999),
    if (LOCATE('8',title)>0,LOCATE('8',title),999),
    if (LOCATE('9',title)>0,LOCATE('9',title),999)
  ) AS firstint_pos FROM products) AS t
ORDER BY
  -- first order by the string before the integer value
  IF (firstint_pos<999, SUBSTR(title,1, firstint_pos), s),
  -- then order by the integer value
  CONVERT(SUBSTR(title, firstint_pos), SIGNED);

Or you may add a sort of sortno column to the table and maintain it...

MySQL Natural ordering of product names

3 Answers3