Most databases are not case sensitive when comparing strings. However, when using SQLite recently, I found that it was exactly the opposite.
Assume that the structure and value of the table User are as follows:
| UserName | |
|---|---|
| 1 | User1 |
Execute the following SQL statement:
Copy the codeThe code is as follows:
SELECT * FROM [User] WHERE UserName = 'user1'
As a result, no records were found. Obviously, SQLite is case sensitive by default when performing string comparisons. This can have adverse effects on the application. For example, when entering a user name, it must strictly enter it in case, which is a very bad user experience; there may be two users, user and USER, in the user table, which can easily cause confusion.
Since SQLite is a database that has only emerged in recent years, there is very little Chinese data. Google has some English materials and finally found three solutions:
Solution 1: Use case conversion functions LOWER and UPPER
Copy the codeThe code is as follows:
SELECT * FROM [User] WHERE LOWER(UserName) = 'user1'
Scheme 2: Force declaration is case-insensitive when comparing
Copy the codeThe code is as follows:
SELECT * FROM [User] WHERE UserName = 'user1' COLLATE NOCASE
Scheme 3: Declare the field case-insensitive when creating a table
Copy the codeThe code is as follows:
CREATE TABLE [User] ( [UserName] NVARCHAR(20) COLLATE NOCASE );
If you don't need to be case sensitive under any circumstances, Solution 3 is the best solution; if you only have a small number of queries that are not case sensitive, Solution 2 can be used. Since the use of functions is used, there may be additional performance losses and is not recommended.