Add "collate nocase" to your queries.
sqlite> select * from A inner join B on A.a=B.a collate nocase;
a b c a d e
---------- ---------- ---------- ---------- ---------- ----------
a 4 7 A 4 7
b 5 8 B 5 8
sqlite> select * from A, B where A.a=B.a collate nocase;
select * from A, B where A.a=B.a collate nocase;
a b c a d e
---------- ---------- ---------- ---------- ---------- ----------
a 4 7 A 4 7
b 5 8 B 5 8
sqlite> select * from A join B on A.a=B.a collate nocase;
select * from A join B on A.a=B.a collate nocase;
a b c a d e
---------- ---------- ---------- ---------- ---------- ----------
a 4 7 A 4 7
b 5 8 B 5 8
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
________________________________
From: sqlite-users-boun...@sqlite.org on behalf of Peng Yu
Sent: Wed 7/14/2010 11:36 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:[sqlite] Case insensitive join available?
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;
EOF
--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
