Re: [sqlite] Case-Insensitive Searches

[Derrell . Lipman]

David Wheeler <[EMAIL PROTECTED]> writes:

> What's the best way to do case-insensitive searches in SQLite, given that
> LOWER() may not work properly with UTF-8 characters? Is LOWER() the best way
> in general, anyhow? And if so, will it use indexes?

Function calls are not indexed in SQLite 2.8, so using lower() would not use
an index.  One option for you, if you can afford the space to duplicate your
data with the duplicate stored as lower case, is to add a column of lower-case
data, and triggers to update it automatically.

    CREATE TABLE x(tAsEntered TEXT PRIMARY KEY, tLowerCase TEXT);

    CREATE TRIGGER x_insert_tr
      AFTER INSERT
      ON x
      FOR EACH ROW
      BEGIN
        UPDATE x
          SET tLowerCase = utf8ToLower(new.tAsEntered);
      END;

    CREATE TRIGGER x_update_tr
      AFTER UPDATE OF tAsEntered
      ON x
      FOR EACH ROW
      BEGIN
        UPDATE x
          SET tLowerCase = utf8ToLower(new.tAsEntered);
      END;

    CREATE INDEX x_tLowerCase_idx ON x(tLowerCase);

With this, you'd be able to search on tLowerCase for a case-insensitive
search, or on tAsEntered for a case-sensitive search.

You'll need to provide the utf8ToLower() function used here, in your native
language.  In C, you'd do this by calling sqlite_create_function().  (In
SQLite 3.0, it's probably called sqlite3_create_function(), but check the
docs.)

SQLite 3.0 provides some COLLATE features which may allow you to do this more
conveniently.

Derrell