Most databases are case insensitive when comparing strings.
However, SQLite is case sensitive.
Assume that the structure and value of the test table are as follows:
_ Id |
Name |
1 |
ABCDE |
2 |
ABCDE |
3 |
ABCDE |
4 |
ABCDE |
5 |
Aaaaa |
6 |
Bbbbb |
Execute the following SQL statement:
Select * from test where name = 'abcde ';
No records are found.
Obviously,SQLite is case sensitive by default when comparing strings..
So how can SQLite be case sensitive? There are three solutions:
Solution 1: Use case-insensitive conversion functions lower and upper
1. Select * from test where lower (name) = 'abcde ';
2. Select * from test where lower (name) = lower ('abcde ');
3. Select * from test where lower (name) = lower ('abcde ');
4. Select * from test where lower (name) = lower ('abcde ');
....
(1). Select * from test where upper (name) = 'abcde ';
(2). Select * from test where upper (name) = upper ('abcde ');
(3). Select * from test where upper (name) = upper ('abcde ');
(4). Select * from test where upper (name) = upper ('abcde ');
.....
The queried records are as follows:
1 |
ABCDE |
2 |
ABCDE |
3 |
ABCDE |
4 |
ABCDE |
Solution 2: forcibly declare case insensitive during comparison
Select * from test where name = 'abcde'Collate nocase;
The queried records are as follows:
1 |
ABCDE |
2 |
ABCDE |
3 |
ABCDE |
4 |
ABCDE |
Solution 3: declare that the field is case insensitive when creating a table
Create Table Test (_ id integer, Name textCollate nocase);
If you do not need to be case sensitive under any circumstances, solution 3 is the best solution;
If a small number of queries are case-insensitive, use solution 2.
Solution 1 is not recommended because functions are used, which may cause extra performance consumption.