Postgres中字母数字不区分大小写的排序 [英] Alphanumeric case in-sensitive sorting in postgres

问题描述

我是发布新手，并且想要对varchar类型的列进行排序。想用下面的例子来解释这个问题：

I am new to postrges and want to sort varchar type columns. want to explain the problem with with below example:

表名：testsorting

table name: testsorting

   order       name
    1            b
    2            B
    3            a
    4            a1
    5            a11
    6            a2
    7            a20
    8            A
    9            a19

区分大小写的排序（postgres中的默认设置） ）：

select name from testsorting order by name;

    A
    B
    a
    a1
    a11
    a19
    a2
    a20
    b

不区分大小写的排序给出：

从测试排序中按UPPER（name）选择名称；

select name from testsorting order by UPPER(name);

      A
      a
      a1
      a11
      a19
      a2
      a20
      B
      b

我如何在postgres中使字母数字不区分大小写排序以得到以下顺序：

          a
          A
          a1
          a2
          a11
          a19
          a20
          b
          B

我不介意大写或小写字母的顺序，但顺序应为 aAbB或 AaBb，并且应该不是 ABab

I wont mind the order for capital or small letters, but the order should be "aAbB" or "AaBb" and should not be "ABab"

请在postgres中建议您是否对此有任何解决方案。

Please suggest if you have any solution to this in postgres.

推荐答案

我的PostgreSQL对您想要的方式进行排序。 PostgreSQL比较字符串的方式由语言环境和排序规则决定。使用 createdb 创建数据库时，有 -l <​​/ code>选项设置区域设置。您也可以使用 psql -l <​​/ code>在环境中检查它的配置方式：

My PostgreSQL sorts the way you want. The way PostgreSQL compares strings is determined by locale and collation. When you create database using createdb there is -l option to set locale. Also you can check how it is configured in your environment using psql -l:

[postgres@test]$ psql -l
List of databases
 Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
---------+----------+----------+------------+------------+-----------------------
 mn_test | postgres | UTF8     | pl_PL.UTF8 | pl_PL.UTF8 |

如您所见，我的数据库使用波兰语排序规则。

As you see my database uses Polish collation.

如果您使用其他排序规则创建数据库，则可以在查询中使用其他排序规则，就像：

If you created database using other collation then you can use other collation in query just like:

SELECT * FROM sort_test ORDER BY name COLLATE "C";
SELECT * FROM sort_test ORDER BY name COLLATE "default";
SELECT * FROM sort_test ORDER BY name COLLATE "pl_PL";

您可以通过以下方式列出可用的排序规则：

You can list available collations by:

SELECT * FROM pg_collation;

已编辑：

哦，我想念'a11'必须在'a2'之前。

Oh, I missed that 'a11' must be before 'a2'.

我不认为标准排序规则可以解决字母数字排序问题。对于这种排序，您将必须像Clodoaldo Neto响应中那样将字符串拆分为多个部分。如果您经常必须订购这种方法，那么另一个有用的选择是将名称字段分成两列。您可以在INSERT和UPDATE上创建触发器，将 name 分为 name_1 和 name_2 ，然后：

I don't think standard collation can solve alphanumeric sorting. For such sorting you will have to split string into parts just like in Clodoaldo Neto response. Another option that is useful if you frequently have to order this way is to separate name field into two columns. You can create trigger on INSERT and UPDATE that split name into name_1 and name_2 and then:

SELECT name FROM sort_test ORDER BY name_1 COLLATE "en_EN", name_2;

（我将排序规则从波兰语更改为英语，您应该使用本机排序规则对诸如aącć等字母进​​行排序）

(I changed collation from Polish into English, you should use your native collation to sort letters like aącć etc)

这篇关于Postgres中字母数字不区分大小写的排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！