如何更改sqlite3数据库的排序规则以区分大小写？ [英] How to change the collation of sqlite3 database to sort case insensitively?

问题描述

我查询了提供排序数据的sqlite3数据库。数据基于 varchar 列名称的列进行排序。现在，当我执行查询时

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 varchar column "Name". Now when I do the query

select * from tableNames Order by Name;

它提供了这样的数据。

    Pen
    Stapler
    pencil

表示正在考虑区分大小写的内容。我想要的方式如下

Means it is considering the case sensitive stuff. The way I want is as follows

    Pen
    pencil
    Stapler

那么我应该在sqlite3数据库中进行哪些更改以获取必要的结果？

So what changes should I make in sqlite3 database for the necessary results?

相关<一个href = https://stackoverflow.com/questions/973541/how-to-set-sqlite3-to-be-case-insensitive-when-string-comparing>如何在比较字符串时将Sqlite3设置为不区分大小写？

推荐答案

SQLite 数据类型文档讨论了用户定义的排序顺序。具体来说，您可以使用COLLATE NOCASE实现您的目标。

The SQLite Datatypes documentation discusses user-defined collation sequences. Specifically you use COLLATE NOCASE to achieve your goal.

他们举一个例子：

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

，请注意：

-使用NOCASE排序规则序列进行分组（即，值
-'abc'和'ABC'放在同一组中）。
选择计数（*）GROUP BY d FROM t1;

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

这篇关于如何更改sqlite3数据库的排序规则以区分大小写？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！