SQLite query ignores case, did you step on the pit?

Objective:

General database query strings are ignored and case-insensitive, but SQLite queries are case-sensitive. However, SQLite supports ignoring case when defining tables/queries.

Add collate when defining database table/query matching nocase

Such as:

CREATE table if not exists tb_test_case (ID text PRIMARY KEY, data text COLLATE nocase);

SELECT * from tb_test_case where data = ' a ' COLLATE nocase;

Go to the Chase:

In the project using SQLite, some data queries need to support ignoring case, using the above method, the problem solved! Wait a minute, let's take a step and talk. Seems to solve the problem, and in most scenarios, there is no problem, but in fact, choose the size of SQLite, it has been buried Thunder , waiting for a sudden day user feedback very strange problem.

SQLite supports querying ASCLL characters ignoring case, but does not support querying wide characters (Roman characters Ⅲ, etc.) ignoring case.

Here's an example to verify:

1: Create table tb_test_case,data field is defined to ignore case

create table if not exists tb_test_case (ID text PRIMARY KEY, data text  collate Nocase
2: Insert two data (PS: lowercase ⅲ of Roman characters Ⅲ)
insert into tb_test_case values (' id1 ', ' iⅲi ');
in SERT into tb_test_case values (' Id2 ', ' iⅲi ');

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

3:select * from tb_test_case where data = ' Iⅲi ';

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

SELECT * from tb_test_case where data = ' Iⅲi ';

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

PS: The results of the above two query statements, including Ⅲ, did not query to lowercase ⅲ;

4:insert into tb_test_case values (' id3 ', ' aaa ');

INSERT into tb_test_case values (' Id4 ', ' AAA ');

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

5:select * from tb_test_case where data = ' AAA ';

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

SELECT * from tb_test_case where data = ' AAA ';

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

PS: By the above query statement, it is shown that the ASCLL character is supported by ignoring the case.

Postscript:

Using SQLite to ignore case, if you can guarantee that a field that supports ignoring size does not contain wide characters , it is no problem to use SQLite queries to ignore case. Otherwise, you have fallen into the pit, consider other options!

PS: Our project uses the Meta data information of SQLite records. When problems arise, the problem of feedback is very strange. Due to first-hand handling of user issues is a colleague of service. The site was destroyed when the researcher analyzed the problem. Analysis of the whole day only to find the cause of the problem. Then design, review the problem solution + coded test for two weeks. Design, review compatibility upgrade program + Coding test for another two weeks.

Because individual level is limited, if has the clerical error, the explanation is unclear, the inquiry unclear place. I also ask you to pass the small friends Haihan, and point out the problem, help me to kill it. Reprint please indicate the source.

SQLite query ignores case, did you step on the pit?