SQLite The case-solving method for string comparisons _sqlite

Most databases are insensitive to capitalization when they are compared to a string. However, when SQLite was recently used, it was found to be the opposite.

Assume that the structure and values of the table user are as follows:

	UserName
1	User1

Execute the following SQL statement:

Copy Code code as follows:

SELECT * FROM [User] WHERE UserName = ' user1 '

As a result, no records were found. Obviously, SQLite is sensitive to capitalization when it makes string comparisons. This can adversely affect the application. For example, users must enter user names strictly according to the case of input, this is a very bad user experience, the user table may exist at the same time user and user two users, easily confusing.

Since SQLite is a database that has arisen in recent years, there are very few Chinese materials. Google has some English materials, and finally found three solutions:

Scenario One: Use case conversion functions lower, UPPER

Copy Code code as follows:

SELECT * FROM [User] WHERE LOWER (UserName) = ' User1 '

Scenario two: Forcing a declaration to be case-insensitive when comparing

Copy Code code as follows:

SELECT * FROM [User] WHERE UserName = ' user1 ' COLLATE nocase

Scenario Three: Declare the field to be case-insensitive when creating a table

Copy Code code as follows:

CREATE TABLE [User] ([UserName] NVARCHAR COLLATE nocase);

If you do not need to be sensitive to case sensitivity under any circumstances, scenario three is the best solution; If only a small number of queries are insensitive to capitalization, you can use scenario two. However, due to the use of the function, there may be additional performance loss, not recommended.