postgresql postgres中的字母数字不区分大小写的排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15427529/
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
Alphanumeric case in-sensitive sorting in postgres
提问by akhi
I am new to postrges and want to sort varchar type columns. want to explain the problem with with below example:
我是 postrges 的新手，想对 varchar 类型的列进行排序。想用下面的例子解释这个问题：
table name: testsorting
表名：testsorting
order name
1 b
2 B
3 a
4 a1
5 a11
6 a2
7 a20
8 A
9 a19
case sensitive sorting (which is default in postgres) gives:
区分大小写的排序（postgres 中的默认设置）给出：
select name from testsorting order by name;
A
B
a
a1
a11
a19
a2
a20
b
case in-sensitive sorting gives:
不区分大小写的排序给出：
select name from testsorting order by UPPER(name);
按 UPPER(name) 从 testsorting order 中选择 name；
A
a
a1
a11
a19
a2
a20
B
b
how can i make alphanumeric case in-sensitive sorting in postgres to get below order:
我如何在 postgres 中进行字母数字大小写不敏感排序以达到以下顺序：
a
A
a1
a2
a11
a19
a20
b
B
I wont mind the order for capital or small letters, but the order should be "aAbB" or "AaBb" and should not be "ABab"
我不介意大写或小写字母的顺序，但顺序应该是“aAbB”或“AaBb”，而不应该是“ABab”
Please suggest if you have any solution to this in postgres.
请建议您是否在 postgres 中对此有任何解决方案。
回答by Micha? Niklas
My PostgreSQL sorts the way you want. The way PostgreSQL compares strings is determined by locale and collation. When you create database using createdbthere is -loption to set locale. Also you can check how it is configured in your environment using psql -l:
我的 PostgreSQL 按照你想要的方式排序。PostgreSQL 比较字符串的方式由语言环境和排序规则决定。当您使用创建数据库时createdb，可以-l选择设置语言环境。您还可以使用以下命令检查它在您的环境中的配置方式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;
EDITED:
编辑：
Oh, I missed that 'a11' must be before 'a2'.
哦，我错过了 'a11' 必须在 'a2' 之前。
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 nameinto name_1and name_2and then:
我认为标准整理不能解决字母数字排序。对于此类排序，您必须像 Clodoaldo Neto 响应一样将字符串拆分为多个部分。如果您经常需要以这种方式进行排序，另一个很有用的选项是将名称字段分成两列。您可以创建INSERT触发器和更新拆分name成name_1和name_2，然后：
SELECT name FROM sort_test ORDER BY name_1 COLLATE "en_EN", name_2;
(I changed collation from Polish into English, you should use your native collation to sort letters like a?c? etc)
（我将校对规则从波兰语更改为英语，您应该使用您的母语校对规则对 a?c? 等字母进行排序）
回答by Clodoaldo Neto
If the name is always in the 1 alpha followed by n numericsformat then:
如果名称始终采用以下1 alpha followed by n numerics格式，则：
select name
from testsorting
order by
upper(left(name, 1)),
(substring(name from 2) || '0')::integer
回答by Chris Aitchison
I agree with Clodoaldo Neto's answer, but also don't forget to add the index
我同意 Clodoaldo Neto 的回答，但也不要忘记添加索引
CREATE INDEX testsorting_name on testsorting(upper(left(name,1)), substring(name from 2)::integer)
回答by Manu Singhal
PostgreSQL uses the C library locale facilities for sorting strings. C library is provided by the host operating system. On Mac OS X or a BSD-family operating system,the UTF-8 locale definitions are broken and hence the results are as per collation "C".
PostgreSQL 使用 C 库语言环境工具对字符串进行排序。C 库由主机操作系统提供。在 Mac OS X 或 BSD 系列操作系统上，UTF-8 语言环境定义被破坏，因此结果按照排序规则“C”。
image attached for collation results with ubuntu 15.04 as host OS
附上图像，用于使用 ubuntu 15.04 作为主机操作系统的整理结果
Check FAQ's on postgres wiki for more details : https://wiki.postgresql.org/wiki/FAQ
查看 postgres wiki 上的常见问题以获取更多详细信息：https: //wiki.postgresql.org/wiki/FAQ
回答by Le Droid
Answer strongly inspired from this one.
By using a function it will be easier to keep it clean if you need it over different queries.
答案的灵感来自于这一点。
如果您在不同的查询中需要它，通过使用一个函数，它会更容易保持干净。
CREATE OR REPLACE FUNCTION alphanum(str anyelement)
RETURNS anyelement AS $$
BEGIN
RETURN (SUBSTRING(str, '^[^0-9]*'),
COALESCE(SUBSTRING(str, '[0-9]+')::INT, -1) + 2000000);
END;
$$ LANGUAGE plpgsql IMMUTABLE;
Then you could use it this way:
那么你可以这样使用它：
SELECT name FROM testsorting ORDER BY alphanum(name);
Test:
测试：
WITH x(name) AS (VALUES ('b'), ('B'), ('a'), ('a1'),
('a11'), ('a2'), ('a20'), ('A'), ('a19'))
SELECT name, alphanum(name) FROM x ORDER BY alphanum(name);
name | alphanum
------+-------------
a | (a,1999999)
A | (A,1999999)
a1 | (a,2000001)
a2 | (a,2000002)
a11 | (a,2000011)
a19 | (a,2000019)
a20 | (a,2000020)
b | (b,1999999)
B | (B,1999999)
回答by Alexis.Rolland
As far as I'm concerned, I have used the PostgreSQL module citextand used the data type CITEXTinstead of TEXT. It makes both sort and search on these columns case insensitive.
就我而言，我使用了 PostgreSQL 模块citext并使用了数据类型CITEXT而不是TEXT. 它使对这些列的排序和搜索不区分大小写。
The module can be installed with the SQL command CREATE EXTENSION IF NOT EXISTS citext;
该模块可以通过 SQL 命令安装CREATE EXTENSION IF NOT EXISTS citext;
