'Order a column in mysql by if it contains a letter

I have a column named convoys in my worker-database and I first want to sort the 'non-letter-containing'-values and after them the 'letter-containing' ones.

For example here are a few values

name convoy
worker1 1
worker2 3
worker3 M 4
worker4 M5
worker5 4
worker6 11

and it should sort them like this

name convoy
worker1 1
worker2 3
worker5 4
worker6 11
worker3 M 4
worker4 M5

Has anybody some kind of idea how to make this query working?

mysqlsqlsortingsql-order-bycolumnsorting


Solution 1:[1]

I think the most direct way to do that would be the following (using regular expression):

SELECT name,
       convoy
FROM TABLE_NAME
ORDER BY CASE WHEN convoy REGEXP '^[0-9]+$' THEN convoy ELSE convoy END

Solution 2:[2]

Order by the first token combined from characters that are not digits or spaces, and then by the first token combined from digits (and converted to UNSIGNED).
Please note that this solution also order correctly strings such as 'M13' (which comes after 'M 4' and 'M5')

select   *
from     t
order by regexp_substr(convoy, '[^\\d\\s]+')
        ,cast(regexp_substr(convoy, '\\d+') as UNSIGNED)

fiddle

Solution 3:[3]

A maximal unsigned integer: 4294967295

MySQL 5.6

This SQL sorts 'non-letter-containing' values as numbers, then others.

select
 name,
 convoy
from Table1
order by
 case when
   convoy REGEXP '^[0-9]+$'
  then convert(convoy, UNSIGNED INTEGER)
  else 4294967295
  end,
convoy
;

Or maybe better:

select
  *
from Table1
order by
 case when
   convoy REGEXP '^[0-9]+$'
  then LPAD(convert(convoy, UNSIGNED INTEGER),10,0)
  else convoy
  end
;

DDL:

CREATE TABLE Table1
    (`name` varchar(7), `convoy` varchar(3))
;
    
INSERT INTO Table1
    (`name`, `convoy`)
VALUES
    ('worker1', '1'),
    ('worker2', '3'),
    ('worker3', 'M 4'),
    ('worker4', 'M5'),
    ('worker5', '4'),
    ('worker6', '11')
;

Output:

name convoy
worker1 1
worker2 3
worker5 4
worker6 11
worker3 M 4
worker4 M5

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 LUKASANUKVARI
Solution 2 David דודו Markovitz
Solution 3

Related Questions