CODEWITHSUNDEEP
mysqlsql-order-by
Gabriel
Gabriel

Reputation: 78

ORDER BY numeric and characters

I want to sort the following data items in the order they are presented below:

ZZZ-AA1
ZZZ-AA2
ZZZ-AA3
ZZZ-AA11
ZZZ-AA12
ZZZ-AAA1
ZZZ-AB1
ZZZ-AB2
ZZZ-BB1
ZZZ-BB12
ZZZ-CA1

Any tricks to make it sort more properly?

Thanks!

Queries that I have tried:

SELECT bay FROM table GROUP BY bay ORDER BY SUBSTR(bay FROM 1 FOR 6), CAST(SUBSTR(bay FROM 6) AS UNSIGNED)
SELECT bay FROM table GROUP BY bay ORDER BY LENGTH(bay), bay

Upvotes: 0

Views: 141

Answers (3)

FanoFN
FanoFN

Reputation: 7124

On MySQL prior to 8.0, trying to achieve this is quite a long way. In MySQL v8.0, one possible solution is using REGEXP_REPLACE like:

SELECT * FROM test
      ORDER BY 
      REGEXP_REPLACE(val, "[0-9]", "") ASC,
      ABS(REGEXP_REPLACE(val, "[A-Za-z]", "")) ASC;

However, since you're using MysQL v5.7, this is what I come up with:

SELECT val FROM 
(SELECT val, 
     LEAST(IF(LOCATE(0, val)=0,999,LOCATE(0, val)), 
     IF(LOCATE(1, val)=0,999,LOCATE(1, val)),  
     IF(LOCATE(2, val)=0,999,LOCATE(2, val)), 
     IF(LOCATE(3, val)=0,999,LOCATE(3, val)),  
     IF(LOCATE(4, val)=0,999,LOCATE(4, val)), 
     IF(LOCATE(5, val)=0,999,LOCATE(5, val)),  
     IF(LOCATE(6, val)=0,999,LOCATE(6, val)), 
     IF(LOCATE(7, val)=0,999,LOCATE(7, val)),  
     IF(LOCATE(8, val)=0,999,LOCATE(8, val)), 
     IF(LOCATE(9, val)=0,999,LOCATE(9, val))) lv
FROM test) t
ORDER BY SUBSTRING(val,1,lv-1) ASC, SUBSTRING(val,lv)+0 ASC;

The idea is to get the first number occurrence then use that to separate between the values before numbers and the numbers itself using LOCATE function:

LOCATE(1, val)
*translate to "Locate the number 1 in `val` column".

IF(LOCATE(1, val)=0, 999, LOCATE(1, val))
*translate to "If you can't locate the number 1 in `val`, return 999 
 otherwise return the location of it.".

Since these are numbers we're looking for, it's much easier because I'll only need to locate 0 to 9 number values. Then, if LOCATE return 0 as result, replace it with 999. I replace them because I'm using LEAST to return the smallest value; which is the first number occurrence:

LEAST(IF(LOCATE ..

After that, I make the query as a sub-query then uses the result from LEAST(IF(LOCATE .. as the centre of SUBSTRING in the ORDER BY:

SUBSTRING(val,1,lv-1)
*Will return the value before the first number occurrence. 
(e.g ZZZ-AA1 to ZZZ-AA OR ZZZ-AAA1 to ZZZ-AAA).

AND 

SUBSTRING(val,lv)
*Will return the value on the first number onwards. 
(e.g. ZZZ-AA1 to 1 OR ZZZ-AAA1 to 1).

Of course, if you want to look at the value, you can simply add the statement in ORDER BY to SELECT like :

SELECT val, SUBSTRING(val,1,lv-1) , SUBSTRING(val,lv)+0  FROM 
...

*If you notice, in the first SUBSTRING I've added lv-1 because without minus 1, the SUBSTRING will return together with the first number occurrence. And the second SUBSTRING I added +0 at the end because without plus 1 the result datatype is string so the ordering will mess up.

Demo fiddle

Upvotes: 1

id'7238
id'7238

Reputation: 2681

In MySQL 5.7, you can try using the SUBSTRING_INDEX function.

SELECT *,
  SUBSTRING_INDEX(
  SUBSTRING_INDEX(
  SUBSTRING_INDEX(
  SUBSTRING_INDEX(
  SUBSTRING_INDEX(
  SUBSTRING_INDEX(
  SUBSTRING_INDEX(
  SUBSTRING_INDEX(
  SUBSTRING_INDEX(
  SUBSTRING_INDEX(bay
    , '1', 1)
    , '2', 1)
    , '3', 1)
    , '4', 1)
    , '5', 1)
    , '6', 1)
    , '7', 1)
    , '8', 1)
    , '9', 1)
    , '0', 1) AS alpha_key
FROM t
ORDER BY alpha_key, CAST(SUBSTRING_INDEX(bay, alpha_key, -1) AS UNSIGNED)

fiddle

Upvotes: 1

Matigo
Matigo

Reputation: 169

You can do this with a pair of REGEXP_SUBSTR():

ORDER BY REGEXP_SUBSTR(tmp.`code`,"[A-Za-z-]+"), CAST(REGEXP_SUBSTR(tmp.`code`,"[0-9]+") AS UNSIGNED)

Here you can see it in action:

SELECT tmp.`code`,
       REGEXP_SUBSTR(tmp.`code`,"[A-Za-z-]+") as `key`,
       CAST(REGEXP_SUBSTR(tmp.`code`,"[0-9]+") AS UNSIGNED) as `sort`
  FROM (SELECT 'ZZZ-AA1' as code UNION ALL
        SELECT 'ZZZ-AA2' as code UNION ALL
        SELECT 'ZZZ-AA3' as code UNION ALL
        SELECT 'ZZZ-AA11' as code UNION ALL
        SELECT 'ZZZ-AA12' as code UNION ALL
        SELECT 'ZZZ-AAA1' as code UNION ALL
        SELECT 'ZZZ-AB1' as code UNION ALL
        SELECT 'ZZZ-AB2' as code UNION ALL
        SELECT 'ZZZ-BB1' as code UNION ALL
        SELECT 'ZZZ-BB12' as code UNION ALL
        SELECT 'ZZZ-CA1' as code) tmp
 ORDER BY REGEXP_SUBSTR(tmp.`code`,"[A-Za-z-]+"),
          CAST(REGEXP_SUBSTR(tmp.`code`,"[0-9]+") AS UNSIGNED);

This will be inefficient when sorting on large datasets, so be sure to use it only with the final subset that is returned.

Upvotes: 0

Related Questions