如何设置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?

我想通过字符串匹配从sqlite3数据库中选择记录。但是如果我在where子句中使用'='，我发现sqlite3是区分大小写的。有人能告诉我如何使用字符串比较不区分大小写吗?

9 个解决方案

#1

396

You can use COLLATE NOCASE in your SELECT query:

您可以在选择查询中使用COLLATE NOCASE:

SELECT * FROM ... WHERE name = 'someone' COLLATE NOCASE

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:

此外，在SQLite中，您可以通过在列定义中指定collate nocase(其他选项是二进制(默认)和rtrim)来指示列在创建表时不区分大小写;见这里)。您还可以在创建索引时指定排序规则nocase。例如:

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);

Expressions involving Test.Text_Value should now be case insensitive. For example:

涉及测试的表达式。Text_Value现在应该是不区分大小写的。例如:

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

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.:

optimiser还可以使用索引在列上进行不区分大小写的搜索和匹配。您可以使用explain SQL命令对此进行检查，例如:

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

#2

140

SELECT * FROM ... WHERE name = 'someone' COLLATE NOCASE

#3

You can do it like this:

你可以这样做:

SELECT * FROM ... WHERE name LIKE 'someone'

(It's not the solution, but in some cases is very convenient)

(这不是解决方案，但在某些情况下非常方便)

"The LIKE operator does a pattern matching comparison. The operand to the right contains the pattern, the left hand operand contains the string to match against the pattern. A percent symbol ("%") in the pattern matches any sequence of zero or more characters in the string. An underscore ("_") in the pattern matches any single character in the string. Any other character matches itself or it's lower/upper case equivalent (i.e. case-insensitive matching). (A bug: SQLite only understands upper/lower case for ASCII characters. The LIKE operator is case sensitive for unicode characters that are beyond the ASCII range. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.)."

LIKE操作符进行模式匹配比较。右边的操作数包含了模式，左边的操作数包含了与模式匹配的字符串。模式中的百分号(“%”)匹配字符串中的任何零或多个字符序列。模式中的下划线(“_”)匹配字符串中的任何单个字符。任何其他字符都匹配它自己或者它的大小写相等(例如大小写不敏感的匹配)。(错误:SQLite只理解ASCII字符的大小写。LIKE操作符对超出ASCII范围的unicode字符区分大小写。例如,表达式' a '像' a '是真的,但“æ”像Æ是假的)。”

#4

This is not specific to sqlite but you can just do

这不是sqlite特有的，但是您可以这样做

SELECT * FROM ... WHERE UPPER(name) = UPPER('someone')

#5

Another option is to create your own custom collation. You can then set that collation on the column or add it to your select clauses. It will be used for ordering and comparisons.

另一个选项是创建您自己的自定义排序。然后可以在列上设置排序或将其添加到select子句中。它将用于排序和比较。

This can be used to make 'VOILA' LIKE 'voilà'.

这可以用来制作“VOILA”，比如“VOILA”。

http://www.sqlite.org/capi3ref.html#sqlite3_create_collation

http://www.sqlite.org/capi3ref.html sqlite3_create_collation

The collating function must return an integer that is negative, zero, or positive if the first string is less than, equal to, or greater than the second, respectively.

如果第一个字符串分别小于、等于或大于第二个字符串，则排序函数必须返回一个为负、零或正的整数。

#6

Another option that may or may not make sense in your case, is to actually have a separate column with pre-lowerscored values of your existing column. This can be populated using the SQLite function LOWER(), and you can then perform matching on this column instead.

在您的例子中，另一个可能有也可能没有意义的选项是，实际上有一个单独的列，其中包含现有列的预标记值。可以使用SQLite函数LOWER()填充它，然后您可以在这个列上执行匹配。

Obviously, it adds redundancy and a potential for inconsistency, but if your data is static it might be a suitable option.

显然，它增加了冗余和不一致的可能性，但是如果您的数据是静态的，那么它可能是一个合适的选择。

#7

If the column is of type char then you need to append the value you are querying with spaces, please refer to this question here . This in addition to using COLLATE NOCASE or one of the other solutions (upper(), etc).

如果列是char类型的，那么您需要附加您正在使用空格查询的值，请参考这里的问题。除了使用COLLATE NOCASE或其他解决方案(upper()等)之外，还可以使用这种方法。

#8

you can use the like query for comparing the respective string with table vales.

您可以使用like查询来比较各自的字符串和表值。

select column name from table_name where column name like 'respective comparing value';

从表_name中选择列名，其中列名如“各自比较值”;

#9

-1

Its working for me Perfectly. SELECT NAME FROM TABLE_NAME WHERE NAME = 'test Name' COLLATE NOCASE

它对我的作用是完美的。从表_name中选择NAME，其中NAME = 'test NAME ' COLLATE NOCASE

#1

396