How to set Sqlite3 to be case insensitive when string comparing?
SqliteCase InsensitiveSqlite Problem Overview
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?
Sqlite Solutions
Solution 1 - Sqlite
You can use COLLATE NOCASE in your SELECT query:
SELECT * FROM ... WHERE name = 'someone' COLLATE NOCASE
Additionaly, 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 http://www.sqlite.org/datatype3.html#collation">here</a>;). 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
Solution 2 - Sqlite
SELECT * FROM ... WHERE name = 'someone' COLLATE NOCASE
Solution 3 - Sqlite
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 its 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.)."
Solution 4 - Sqlite
This is not specific to sqlite but you can just do
SELECT * FROM ... WHERE UPPER(name) = UPPER('someone')
Solution 5 - Sqlite
Another option is to create your own custom collation. You can then set that collation on the column or add it to your select clauses. It will be used for ordering and comparisons.
This can be used to make 'VOILA' LIKE 'voilà'.
http://www.sqlite.org/capi3ref.html#sqlite3_create_collation
> The collating function must return an integer that is negative, zero, or positive if the first string is less than, equal to, or greater than the second, respectively.
Solution 6 - Sqlite
Another option that may or may not make sense in your case, is to actually have a separate column with pre-lowerscored values of your existing column. This can be populated using the SQLite function LOWER(), and you can then perform matching on this column instead.
Obviously, it adds redundancy and a potential for inconsistency, but if your data is static it might be a suitable option.
Solution 7 - Sqlite
If the column is of type char then you need to append the value you are querying with spaces, please refer to this question here . This in addition to using COLLATE NOCASE or one of the other solutions (upper(), etc).
Solution 8 - Sqlite
Its working for me Perfectly.
SELECT NAME FROM TABLE_NAME WHERE NAME = 'test Name' COLLATE NOCASE
Solution 9 - Sqlite
Simply, you can use COLLATE NOCASE in your SELECT query:
SELECT * FROM ... WHERE name = 'someone' COLLATE NOCASE
Solution 10 - Sqlite
use like this
"select * from $pwsXDataHistory where type = '$type' COLLATE NOCASE and $t_uStatus != '$DELETE' order by $t_name COLLATE NOCASE asc ");
Solution 11 - Sqlite
you can use the like query for comparing the respective string with table vales.
select column name from table_name where column name like 'respective comparing value';