On Wed, Jul 14, 2010 at 11:36:06AM -0500, Peng Yu scratched on the wall:
> Hi,
>
> I'm wondering if there is a syntax to do case insensitive join. Of
> course, I could generate two new tables, with the both joining columns
> converted to lower case (or upper case). But I'd like a simpler
> solution.
>
> #!/usr/bin/env bash
>
> rm -f main.db
> sqlite3 main.db <<EOF
>
> create table A (a text, b integer, c integer);
> create table B (a text, d integer, e integer);
> insert into A values('a', 4, 7);
> insert into A values('b', 5, 8);
> insert into A values('c', 6, 9);
> insert into B values('A', 4, 7);
> insert into B values('B', 5, 8);
> insert into B values('D', 6, 9);
>
> .mode column
> .headers on
> .echo on
> select * from A, B where A.a=B.a;
> select * from A inner join B on A.a=B.a;
> select * from A join B on A.a=B.a;
Replace "A.a=B.a" with "A.a COLLATE NOCASE = B.a"
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
