CODEWITHSUNDEEP
androidsqliteandroid-sqlite
Sushil Solanki
Sushil Solanki

Reputation: 71

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

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?

Upvotes: 1

Views: 264

Answers (1)

forpas
forpas

Reputation: 164139

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

Upvotes: 1

Related Questions