CODEWITHSUNDEEP
sqlsqlitecollation
hyperspasm
hyperspasm

Reputation: 1283

sqlite query to return ordered case-sensitive AND case-insensitive hits

Lets say a I have database with a table of words, like so:

CREATE TABLE Words (
  Id integer PRIMARY KEY NOT NULL,
  Word text NOT NULL
);
CREATE INDEX Word_Index ON Words (Word ASC);

sqlite> SELECT * FROM Words;
Id|Word
1|apple
2|Apple
3|Jack
4|jack

To keep things simple, lets just say that I only care about ascii characters, as far as case sensitivity goes.

What I would like to do is perform a search for Word and first return the row that returns the exact case sensitive match, followed by all rows that match, ignoring case, without duplicates. So for example, SELECT * FROM Words WHERE … ‘Apple’ would return:

2|Apple
1|apple

and likewise, SELECT * FROM Words WHERE … ‘apple’ would return:

1|apple
2|Apple

I’m primarily concerned with the case sensitive matches, but would like them to be followed by case-insensitive matches, as a fallback. I’m expecting that usually I’ll get hits for the case-sensitive matches, which is why I have a case-sensitive index. I realize that the case-insensitive fallback won’t be able to use the index, but I’m opting to not have a second (COLLATE NOCASE) index in order to save space in my database, since it would probably be used infrequently anyway. Usually I'm only going to be stepping once, grabbing the first hit.

What’s the most efficient way to do this?

Upvotes: 0

Views: 508

Answers (2)

CL.
CL.

Reputation: 180210

To make both case-sensitive and case-insensitive searches efficient, you need two indexes:

CREATE INDEX Word_Index ON Words (Word);
CREATE INDEX Word_Index_nocase ON Words (Word COLLATE NOCASE);

It is not possible to use efficient index lookups when you're doing tricks with ORDER BY; you have to do separate lookups for case-sensitive and case-insensitive matches, and filter out the duplicates from the second result:

SELECT *
FROM Words
WHERE Word = 'Apple'

UNION ALL

SELECT *
FROM Words
WHERE Word COLLATE NOCASE = 'Apple'
  AND Word <> 'Apple';

(To handle non-ASCII characters, you'd need to install a custom collation).

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270763

I think you want something like this:

SELECT *
FROM Words
WHERE LOWER(col) = LOWER('Apple')
ORDER BY (CASE WHEN col = 'Apple' THEN 1 ELSE 2 END),
         col;

SQLite is case sensitive by default.

You can try this, but I don't know if it will use the index:

SELECT *
FROM Words
WHERE col = 'apple' COLLATE NO CASE
ORDER BY (CASE WHEN col = 'Apple' THEN 1 ELSE 2 END),
         col;

Upvotes: 0

Related Questions