Android SQLiteDatabase query sorting order Alphabet first then numbers and special characters

Question

I have data in my database like this:

Abc
Rsy
456
Zur
(67
123
DYU
*90

I want to sorting like this:-

Abc
DYU
Rsy
Zur
123
456
(67
*90

I'm using the below Query to fetch data:-

SELECT * FROM list WHERE order by 
CASE WHEN name GLOB '[A-Za-z]*' THEN name ELSE '~' || name END

Using the above QUERY, I'm getting the special characters before the numbers. So How can I get the above sorting order?

Answer 1

In your CASE expression all branches return the same column, but it should return different values depending on your conditions:

SELECT * FROM list 
ORDER BY CASE 
    WHEN name GLOB '[A-Za-z]*' THEN 1
    WHEN name GLOB '[0-9]*' THEN 2  
    ELSE 3 
  END,
  name