Sorting special characters?
I have a tablename contacts
that has columns [ "id","name","age" ]
. I need to get all contacts by name in ascending order. I wrote the following request for it:
Select * from contacts order by name collate nocase asc;
The results obtained from the above query are as follows:
1. 11 | #ax Nene | 21
1. 21 | 123 Ray | 22
1. 33 | maxy Wel | 25
1. 41 | Max Vele | 23
1. 53 | Nam sing | 25
The above order is fine, but I want names starting with special characters [#, $, or any other non-alphabet] at the bottom of the results, not at the top. What should I change in my query to achieve the desired results.
NOTE. I am using sqlite.
source to share
You need to add some character like ~
(which is sorted after letters) to any such string. When you only do this in the ORDER BY clause, it only affects the sorting, not the return values:
SELECT *
FROM contacts
ORDER BY CASE WHEN name GLOB '[A-Za-z]*'
THEN name
ELSE '~' || name
END COLLATE NOCASE;
( COLLATE NOCASE
makes case insensitive.)
source to share