如何更改sqlite3数据库的排序规则以不区分大小写排序？

发布于 2024-07-29 03:56:22 字数 538 浏览 7 评论 0原文

我有一个对 sqlite3 数据库的查询，它提供了排序的数据。数据根据 varchar 列“Name”进行排序。现在，当我进行查询时，

select * from tableNames Order by Name;

它提供这样的数据。

    Pen
    Stapler
    pencil

意味着它正在考虑区分大小写的内容。我想要的方式如下

    Pen
    pencil
    Stapler

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

相关如何将Sqlite3设置为字符串比较时不区分大小写？

原文

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;

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?

Related How to set Sqlite3 to be case insensitive when string comparing?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

苏大泽ㄣ 2024-08-05 03:56:22

要对其进行排序，不区分大小写，您可以使用 ORDER BY Name COLLATE NOCASE

回复收藏 0 原文

雪若未夕 2024-08-05 03:56:22

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

他们给出了一个例子：

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' 被放置在同一组中）。
SELECT count(*) GROUP BY d FROM t1；

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

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;

回复收藏 0 原文

信仰 2024-08-05 03:56:22

select * from tableNames Order by lower(Name);

Michael van der Westhuizen 在下面的评论中解释了为什么这不是一个好方法。我保留这个答案是为了保留他的评论，并向其他可能有与我相同“聪明”想法的人发出警告；-)

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

回复收藏 0 原文