And I missed one more...so many solutions...so little time...
CREATE TABLE userTable (name VARCHAR COLLATE NOCASE);
SELECT x from userTable where name='name';
Then you'll get all case-insensitive matches too.
All depends on whether or not you need to really keep the data in it's original
form. I would favor normalizing the data on the INSERT.
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
________________________________
From: sqlite-users-boun...@sqlite.org on behalf of Black, Michael (IS)
Sent: Mon 5/3/2010 5:58 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Optimising usage of LIKE
Simon's answer is probably best -- without any benchmarks it makes the most
sense.
You've got at least two solutions that don't require changing your data:
SELECT x FROM userTable WHERE upper(name) = upper('name');
SELECT x FROM userTable WHERE name = 'name' COLLATE NOCASE.
And one solution if it is possible to store you're data in upper case.
INSERT INTO userTable values(upper(name));
Then the indexes will all be correct, you can detect duplicates, and should cut
the index searching in half.
SELECT x FROM userTable where name = upper('name);
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
________________________________
From: sqlite-users-boun...@sqlite.org on behalf of Simon Slavin
Sent: Mon 5/3/2010 5:33 AM
To: i...@omroth.com; General Discussion of SQLite Database
Subject: Re: [sqlite] Optimising usage of LIKE
On 3 May 2010, at 9:53am, Ian Hardingham wrote:
> For various embarrassing reasons, I'm using:
>
> SELECT x FROM userTable WHERE name LIKE 'name'
>
> To look up entries in my account table. Basically, the scripting
> language I'm using which hooks into SQLite is a bit case-agnostic.
>
> I've been told by a friend that this is extremely inefficient, and that
> I should UPPER my 'name' column and my query name in order to get it to
> be fast.
>
> Can anyone tell me the easiest way of doing this?
Your friend is used to other versions of SQLite. Your best bet is to define
the field you're searching on as having COLLATE NOCASE. This means that all
indexing and searching on it will ignore case. See sections 6 and 6.3 in
http://www.sqlite.org/datatype3.html
Simon.
_______________________________________________
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
