[sqlite] SQL Help: Finding case insensitive matches

[Clemens Ladisch]

Dominique Devienne wrote:
> Imagine I have a single table containing a single text column, of user names.
> I'm trying to find user names which differ only by case.
>
>  select u1.user, u2.user from os_users u1, os_users u2 where upper(u1.user) = 
> upper(u2.user) and u1.user <> u2.user;
>  select user from os_users u where exists (select 1 from os_users u2 where 
> upper(u2.user) = upper(u.user) and u2.user <> u.user);
>
> I tried both queries above, but they are slow (almost 3s, with close
> to 4M steps, i.e. O(N^2))

You can get O(N * log N) with a proper index, and avoiding the function:

 CREATE INDEX u_nocase ON os_users(user COLLATE NOCASE);

 SELECT u1.user, u2.user
 FROM os_users u1
 JOIN os_users u2 ON u1.user COLLATE NOCASE = u2.user COLLATE NOCASE
                 AND u1.user <> u2.user;

(Same with the other query.)

With a recent enough SQLite, you can use an expression index without
changing the queries:

 CREATE INDEX u_upper ON os_users(upper(user));


Regards,
Clemens