themca themca - 2 months ago 6
MySQL Question

MySQL + PHP order by combined string

I'm developing a voucher plugin for my girlfriends business.
the voucher-id (vid) looks like this 2016-1, first part is the current year and the second part is the current voucher id.

My problem is now to order the vids ASC / DESC.

The output now is:

- 2016-1
- 2016-10
- 2016-11
- 2016-12
- 2016-13
- 2016-2
- 2016-20
- 2016-21
- etc.


but it should be:

- 2016-1
- 2016-2
- 2016-..
- 2016-9
- 2016-10
- 2016-11
- 2016-..
- 2016-19
- 2016-20
- 2016-21
- 2016-..


I think casting is no option because of the "-".

Hope anyone can help me!

regards

M.

Answer

If all your data in this format (xxxx-yy) then you can try the following order by clause :

ORDER BY SUBSTRING_INDEX(your_column,'-',1)+0,
SUBSTRING_INDEX(your_column,'-',-1)+0

Demonstration:

SET @str := '2016-01';

SELECT 
SUBSTRING_INDEX(@str,'-',1)+0 AS firstPart,
SUBSTRING_INDEX(@str,'-',-1)+0 AS secondPart;

Output:

firstPart   secondPart
  2016          1

Note:

the first substring_index function grabs the text before the hyphen and the second substring_index function grabs the text after the hyphen.

Later adding 0 to it converts it to a number.

More: Alternatively you can use CAST function like below:

ORDER BY
CAST(SUBSTRING_INDEX(your_column,'-',1)) AS UNSIGNED) ,
CAST(SUBSTRING_INDEX(your_column,'-',-1)) AS UNSIGNED) 
Comments