Case-insensitive using 'ORDER BY'
(1) By Trudge on 2020-11-04 23:20:47 [link] [source]
I'm confused a bit by the ORDER BY operator. I would like it to be case-insensitive. I already have enabled 'case_sensitive_like = 1' but this seems to only apply when using LIKE, and it does work as it should.
Is it possible to have the same feature available when doing an 'ORDER BY'?
This CLI statement is NOT case-sensitive for 'name':
sqlite> select name,title from artists join cds on artists.artistid=cds.artistid where cds.genre like 'Amb%' and name like '%' order by name asc,title;
VA Hed Music|Hed Music 116
VA Hed Music|Hed Music 117
VA Hed Music|Hed Music 118
VA Hed Music|Hed Music 119
VA Hed Music|Hed Music 126
VA Hed Music|Hed Music 127
Zero 7|When It Falls
dZihan & Kamien|Gran Riserva (2002)
Perhaps I'm doing something wrong which is not surprising, I'm fairly new to sqlite. Any help is much appreciated.
(2) By Keith Medcalf (kmedcalf) on 2020-11-04 23:36:02 in reply to 1 [source]
If you want to change the collation (sort order) for text data, you need to specify the name of the collation you want to use, either in the table definition for the column if the column will always use that collation by default rather than the BINARY collation (which is the default if you do not specify one), or, where you want to change the collation/sort/compare sequence for one operation only, then for that one operation.
CREATE TABLE x (Name TEXT NOT NULL COLLATE NOCASE);
CREATE INDEX a ON b (whuppy COLLATE NOCASE);
SELECT diddly FROM bo ORDER BY diddly COLLATE NOCASE;
SELECT * FROM yippee WHERE twat == 'for sure' COLLATE NOCASE;
So you need to specify the collation that you want to use when sorting/comparing in the order by clause, as in:
select name,title from artists join cds on artists.artistid=cds.artistid where cds.genre like 'Amb%' and name like '%' order by name collate nocase,title;
https://sqlite.org/datatype3.html#collating_sequences for documentation
(3) By Trudge on 2020-11-04 23:40:09 in reply to 2 [link] [source]
Excellent, Exactly what I was looking for. Thank you (again).