CODEWITHSUNDEEP
mysqlsortingcolumnsorting
zgormez
zgormez

Reputation: 459

MYSQL- sorting varchar column includes number and char

id column have these values: 2,10,X,Y,D,G

I want to sort like this: 2,10,D,G,X,Y

I tried many solutions, and results are here

http://sqlfiddle.com/#!2/846ed/1

  1. order by id*1;

    sorted results:G X Y D 2 10

  2. order by LPAD(id, 20, '0');

    sorted results:2 D G X Y 10

  3. order by CASE WHEN id REGEXP '^[0-9]+$' THEN id*1 else 999999 END;

    sorted results:2 10 G X Y D

Upvotes: 0

Views: 76

Answers (1)

Oscar Pérez
Oscar Pérez

Reputation: 4397

You need to use different sorting values:

  SELECT id 
    FROM test
ORDER BY (case when id REGEXP('(^[0-9]+$)') then 0 else 1 end), 
         cast(id as unsigned), 
         id

Upvotes: 1

Related Questions