字符串比较时如何将Sqlite3设置为不区分大小写？ [英] How to set Sqlite3 to be case insensitive when string comparing?
问题描述
我想通过字符串匹配从sqlite3数据库中选择记录。但是，如果我在where子句中使用 =，则会发现sqlite3区分大小写。谁能告诉我如何使用不区分大小写的字符串比较？
I want to select records from sqlite3 database by string matching. But if I use '=' in the where clause, I found that sqlite3 is case sensitive. Can anyone tell me how to use string comparing case-insensitive?
推荐答案
您可以使用 COLLATE NOCASE 在您的 SELECT 查询中：
You can use COLLATE NOCASE in your SELECT query:
SELECT * FROM ... WHERE name = 'someone' COLLATE NOCASE
另外，在SQLite中，您可以指示通过在列定义中指定 collate nocase 创建表时，该列应不区分大小写（其他选项为 binary （默认）和 rtrim ；请参见此处）。您还可以在创建索引时指定分页无大小写。例如：
Additionaly, in SQLite, you can indicate that a column should be case insensitive when you create the table by specifying collate nocase in the column definition (the other options are binary (the default) and rtrim; see here). You can specify collate nocase when you create an index as well. For example:
create table Test
(
Text_Value text collate nocase
);
insert into Test values ('A');
insert into Test values ('b');
insert into Test values ('C');
create index Test_Text_Value_Index
on Test (Text_Value collate nocase);
涉及 Test.Text_Value 的表达式现在应不区分大小写。例如：
Expressions involving Test.Text_Value should now be case insensitive. For example:
sqlite> select Text_Value from Test where Text_Value = 'B';
Text_Value
----------------
b
sqlite> select Text_Value from Test order by Text_Value;
Text_Value
----------------
A
b
C
sqlite> select Text_Value from Test order by Text_Value desc;
Text_Value
----------------
C
b
A
优化器还可以潜在地利用索引进行不区分大小写的列搜索和匹配。您可以使用 explain SQL命令进行检查，例如：
The optimiser can also potentially make use of the index for case-insensitive searching and matching on the column. You can check this using the explain SQL command, e.g.:
sqlite> explain select Text_Value from Test where Text_Value = 'b';
addr opcode p1 p2 p3
---------------- -------------- ---------- ---------- ---------------------------------
0 Goto 0 16
1 Integer 0 0
2 OpenRead 1 3 keyinfo(1,NOCASE)
3 SetNumColumns 1 2
4 String8 0 0 b
5 IsNull -1 14
6 MakeRecord 1 0 a
7 MemStore 0 0
8 MoveGe 1 14
9 MemLoad 0 0
10 IdxGE 1 14 +
11 Column 1 0
12 Callback 1 0
13 Next 1 9
14 Close 1 0
15 Halt 0 0
16 Transaction 0 0
17 VerifyCookie 0 4
18 Goto 0 1
19 Noop 0 0
这篇关于字符串比较时如何将Sqlite3设置为不区分大小写？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
