sqlite and collate nocase

Databases
1

Given these titles in a table:

The Zoo The Bug Bugs anteater

I’m trying to do a case-insensitive sort:

select title from titles order by title collate nocase

This gives me:

anteater Bugs The Bug The Zoo

So far so good. But for titles that begin with "a ", "an " or "the " (note the trailing space), I want to sort on the second word in the title. So I used a case statement in the order by clause:

select title from titles order by case when substr(title, 1, 2) = 'a ' COLLATE NOCASE then substr(title, 3) COLLATE NOCASE when substr(title, 1, 3) = 'an ' COLLATE NOCASE then substr(title, 4) COLLATE NOCASE when substr(title, 1, 4) = 'the ' COLLATE NOCASE then substr(title, 5) COLLATE NOCASE else title COLLATE NOCASE end

This gives me:

The Bug Bugs The Zoo anteater

So it is sorting on the second word for "a ", "an " or "the " titles, but now the lowercase title is at the end, despite the collate nocase in the last part of the case statement. Any ideas on where this is messed up?

For the record, I also used Like with the same results:

select title from cb_titles order by case when title like 'a %' COLLATE NOCASE then substr(title, 3) COLLATE NOCASE when title like 'an %' COLLATE NOCASE then substr(title, 4) COLLATE NOCASE when title like 'the %' COLLATE NOCASE then substr(title, 5) COLLATE NOCASE else title COLLATE NOCASE end

2

What does something like the following do ?

select title
from titles
order by
case
when lower(substr(title, 1, 2)) = 'a ’ then lower(substr(title, 3))
when lower(substr(title, 1, 3)) = 'an ’ then lower(substr(title, 4))
when lower(substr(title, 1, 4)) = 'the ’ then lower(substr(title, 5))
else lower(title) end

3

I should have thought of that but yes, that works. Thanks. Is there anything obviously wrong with my collate nocase usage or maybe just a limitation/bug when using it in a case statement?

4

I dont see anything inherently wrong with the sql you had
Even the notes about sqlite’s collate no case dont lead me to any better understanding of why this would be the way it is in your query