views:

4729

answers:

5

I want to select records from sqlite3 database by string matching. But if I use '=' in the where clause, I found that sqlite3 is case sensitive. Can anyone tell me how to use string comparing case-insensitive? Thank you very much!

+4  A: 

This is not specific to sqlite but you can just do

SELECT * FROM ... WHERE UPPER(name) = UPPER('someone')
oykuo
Is there any loss of performance by doing this?
quantity
There's a loss in performance for any operation. In this case it's negligible. If you want to improve performance make sure to put the string you're searching for into upper case so that SQLITE doesn't have to.
Spencer Ruport
The other part of the performance concern is finding the matching rows in the table. Does SQLite3 support function-based indexes? Indexing the search column or expression (e.g. "UPPER(name)") in a situation like this is usually a good idea.
cheduardo
A: 

You can do it like this:

SELECT * FROM ... WHERE name LIKE 'someone'

(It's not the solution, but in some cases is very convenient)

"The LIKE operator does a pattern matching comparison. The operand to the right contains the pattern, the left hand operand contains the string to match against the pattern. A percent symbol ("%") in the pattern matches any sequence of zero or more characters in the string. An underscore ("_") in the pattern matches any single character in the string. Any other character matches itself or it's lower/upper case equivalent (i.e. case-insensitive matching). (A bug: SQLite only understands upper/lower case for ASCII characters. The LIKE operator is case sensitive for unicode characters that are beyond the ASCII range. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.)."

Nick D
+7  A: 
SELECT * FROM ... WHERE name = 'someone' COLLATE NOCASE
Craz
+5  A: 

In SQLite, you can indicate that a column should be case insensitive when you create the table by specifying collate nocase in the column definition (the other options are binary (the default) and rtrim; see here). You can specify collate nocase when you create an index as well. For example:

create table Test
(
  Text_Value  text collate nocase
);

insert into Test values ('A');
insert into Test values ('b');
insert into Test values ('C');

create index Test_Text_Value_Index
  on Test (Text_Value collate nocase);

Expressions involving Test.Text_Value should now be case insensitive. For example:

sqlite> select Text_Value from Test where Text_Value = 'B';
Text_Value      
----------------
b               

sqlite> select Text_Value from Test order by Text_Value;
Text_Value      
----------------
A               
b               
C    

sqlite> select Text_Value from Test order by Text_Value desc;
Text_Value      
----------------
C               
b               
A               

The optimiser can also potentially make use of the index for case-insensitive searching and matching on the column. You can check this using the explain SQL command, e.g.:

sqlite> explain select Text_Value from Test where Text_Value = 'b';
addr              opcode          p1          p2          p3                               
----------------  --------------  ----------  ----------  ---------------------------------
0                 Goto            0           16                                           
1                 Integer         0           0                                            
2                 OpenRead        1           3           keyinfo(1,NOCASE)                
3                 SetNumColumns   1           2                                            
4                 String8         0           0           b                                
5                 IsNull          -1          14                                           
6                 MakeRecord      1           0           a                                
7                 MemStore        0           0                                            
8                 MoveGe          1           14                                           
9                 MemLoad         0           0                                            
10                IdxGE           1           14          +                                
11                Column          1           0                                            
12                Callback        1           0                                            
13                Next            1           9                                            
14                Close           1           0                                            
15                Halt            0           0                                            
16                Transaction     0           0                                            
17                VerifyCookie    0           4                                            
18                Goto            0           1                                            
19                Noop            0           0                                            
cheduardo
A: 

Here's a probable solution for you

http://www.codeproject.com/KB/database/SQLiteUTF8CIComparison.aspx

Dmitry