SQL 如何更改sqlite3数据库的排序规则以不区分大小写？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1188749/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to change the collation of sqlite3 database to sort case insensitively?
提问by rkb
I have a query for sqlite3 database which provides the sorted data. The data are sorted on the basis of a column which is a varcharcolumn "Name". Now when I do the query
我有一个对提供排序数据的 sqlite3 数据库的查询。数据基于varchar列“名称”进行排序。现在当我做查询时
select * from tableNames Order by Name;
It provides the data like this.
它提供这样的数据。
Pen
Stapler
pencil
Means it is considering the case sensitive stuff. The way I want is as follows
意味着它正在考虑区分大小写的东西。我想要的方式如下
Pen
pencil
Stapler
So what changes should I make in sqlite3 database for the necessary results?
那么我应该在 sqlite3 数据库中进行哪些更改以获得必要的结果？
Related How to set Sqlite3 to be case insensitive when string comparing?
采纳答案by Dror Harari
The SQLite Datatypes documentationdiscusses user-defined collation sequences. Specifically you use COLLATE NOCASE to achieve your goal.
SQLite数据类型文档讨论了用户定义的归类序列。具体来说，您使用 COLLATE NOCASE 来实现您的目标。
They give an example:
他们举了一个例子：
CREATE TABLE t1(
a, -- default collation type BINARY
b COLLATE BINARY, -- default collation type BINARY
c COLLATE REVERSE, -- default collation type REVERSE
d COLLATE NOCASE -- default collation type NOCASE
);
and note that:
并注意：
-- Grouping is performed using the NOCASE collation sequence (i.e. values -- 'abc' and 'ABC' are placed in the same group). SELECT count(*) GROUP BY d FROM t1;
-- 使用 NOCASE 整理序列进行分组（即值 -- 'abc' 和 'ABC' 被放置在同一组中）。SELECT count(*) GROUP BY d FROM t1;
回答by Haris Custo
To sort it Case insensitive you can use ORDER BY Name COLLATE NOCASE
要对其进行排序，您可以使用不区分大小写的 ORDER BY Name COLLATE NOCASE
回答by Sinan ünür
select * from tableNames Order by lower(Name);
Michael van der Westhuizen explains in his comment below why this is not a good way. I am leaving this answer up so as to preserve his comment and to serve as a warning to others who might have the same 'bright' idea I had ;-)
Michael van der Westhuizen 在下面的评论中解释了为什么这不是一个好方法。我留下这个答案是为了保留他的评论，并警告其他可能与我有同样“聪明”想法的人;-)
回答by ademus
Use this statement in your SQLite database:
在您的 SQLite 数据库中使用此语句：
PRAGMA case_sensitive_like = false
