[sqlite] SQL Help: Finding case insensitive matches

2016-05-13 Thread Dominique Devienne
On Fri, May 13, 2016 at 6:06 PM, Igor Tandetnik wrote: > On 5/13/2016 11:51 AM, Dominique Devienne wrote: >> But it still returns both rows. Any idea on that part? > > Change "u1.user <> u2.user" to "u1.user < u2.user" Seems obvious once someone clever shows you, yet isn't... Thanks Igor!

[sqlite] SQL Help: Finding case insensitive matches

2016-05-13 Thread Dominique Devienne
On Fri, May 13, 2016 at 5:27 PM, John McKown wrote: >> > select group_concat(user) from os_users group by upper(user) having >> count(*) > 1 >> foo,FOO >> > > how about: SELECT group_concat(user,"|") FROM os_users GROUP BY > upper(user) HAVING count(*) > 1 Thanks John. Sorry, I wasn't clear.

[sqlite] SQL Help: Finding case insensitive matches

2016-05-13 Thread Dominique Devienne
On Fri, May 13, 2016 at 5:23 PM, Clemens Ladisch wrote: > Dominique Devienne wrote: >> select u1.user, u2.user from os_users u1, os_users u2 where upper(u1.user) >> = upper(u2.user) and u1.user <> u2.user; >> [...] but they are slow (almost 3s, with close to 4M steps, i.e. O(N^2)) > > You can

[sqlite] SQL Help: Finding case insensitive matches

2016-05-13 Thread 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

[sqlite] SQL Help: Finding case insensitive matches

2016-05-13 Thread Dominique Devienne
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 count(*) from os_users 1969 > select u1.user, u2.user from os_users u1, os_users u2 where upper(u1.user) = > upper(u2.user) and u1.user <> u2.user

[sqlite] SQL Help: Finding case insensitive matches

2016-05-13 Thread Igor Tandetnik
On 5/13/2016 11:51 AM, Dominique Devienne wrote: > But it still returns both rows. Any idea on that part? Change "u1.user <> u2.user" to "u1.user < u2.user" -- Igor Tandetnik

[sqlite] SQL Help: Finding case insensitive matches

2016-05-13 Thread John McKown
On Fri, May 13, 2016 at 10:14 AM, 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 count(*) from os_users > 1969 > > > select u1.user, u2.user from os_users u1,