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

and it should sort them like this

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

CodePudding user response：

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

CodePudding user response：

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: