CODEWITHSUNDEEP
mysqlsorting
themca
themca

Reputation: 323

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.

Upvotes: 1

Views: 62

Answers (2)

1000111
1000111

Reputation: 13519

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)

Upvotes: 3

Caspar Wylie
Caspar Wylie

Reputation: 2833

You should do it the other way around like voucherid - year. Then the ordering will work.

Or, if you want to keep it the same, try something like this

ORDER BY
    CAST(SUBSTRING(voucher, 5) AS INT) DESC

where 1,5 is "2016-" so it orders by whatever is after: the voucher id.

Upvotes: 1

Related Questions