SQLite case-sensitive Query

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.