Postgresql 排序混合字母数字数据

Postgresql sorting mixed alphanumeric data

提问by el_quick

Running this query:

运行此查询：

select name from folders order by name

returns these results:

返回这些结果：

alphanumeric
a test
test 20
test 19
test 1
test 10

But I expected:

但我预计：

a test
alphanumeric
test 1
test 10
test 19
test 20

What's wrong here?

这里有什么问题？

回答by Grzegorz Szpetkowski

You can simply cast namecolumn to byteadata type allowing collate-agnostic ordering:

您可以简单地将name列转换bytea为允许整理不可知排序的数据类型：

SELECT name
FROM folders
ORDER BY name::bytea;

Result:

结果：

     name     
--------------
 a test
 alphanumeric
 test 1
 test 10
 test 19
 test 20
(6 rows)

回答by A Vlk

All of this methods sorted my selection in alphabetical order:

所有这些方法都按字母顺序对我的选择进行了排序：

test 1
test 10
test 2
test 20

This solution worked for me (lc_collate: 'ru_RU.UTF8'):

这个解决方案对我有用（lc_collat​​e：'ru_RU.UTF8'）：

SELECT name
FROM folders
ORDER BY SUBSTRING(name FROM '([0-9]+)')::BIGINT ASC, name;

test 1
test 2
test 10
test 20

回答by OverZealous

You may be able to manually sort by splitting the text up in case there is trailing numerals, like so:

如果有尾随数字，您可以通过拆分文本来手动排序，如下所示：

SELECT * FROM sort_test
ORDER BY SUBSTRING(text FROM '^(.*?)( \d+)?$'),
         COALESCE(SUBSTRING(text FROM ' (\d+)$')::INTEGER, 0);

This will sort on column text, first by all characters optionally excluding an ending space followed by digits, then by those optional digits.

这将按列文本排序，首先按所有字符（可选地不包括结尾空格后跟数字）排序，然后按那些可选数字排序。

Worked well in my test.

在我的测试中运行良好。

Updatefixed the string-only sorting with a simple coalesce (duh).

更新使用简单的合并 (duh) 修复了仅字符串排序。

回答by Tor H?ndevik

OverZealous answer helped me but didn't work if the string in the database begun with numbers followed by additional characters.

OverZealous 的答案对我有帮助，但如果数据库中的字符串以数字开头，后跟其他字符，则它不起作用。

The following worked for me:

以下对我有用：

SELECT name
FROM folders
ORDER BY
COALESCE(SUBSTRING(name FROM '^(\d+)')::INTEGER, 99999999),
SUBSTRING(name FROM '^\d* *(.*?)( \d+)?$'),
COALESCE(SUBSTRING(name FROM ' (\d+)$')::INTEGER, 0),
name;

So this one:

所以这个：

1. Extracts the first number in the string, or uses 99999999.
2. Extracts the string that follows the possible first number.
3. Extracts a trailing number, or uses 0.

1. 提取字符串中的第一个数字，或使用 99999999。
2. 提取可能的第一个数字后面的字符串。
3. 提取尾随数字，或使用 0。

回答by Deepak Pandey

select * from "public"."directory" where "directoryId" = 17888 order by
COALESCE(SUBSTRING("name" FROM '^(\d+)')::INTEGER, 99999999),
SUBSTRING("name" FROM '[a-zA-z_-]+'),
COALESCE(SUBSTRING("name" FROM '(\d+)$')::INTEGER, 0),
"name";

NOTE:Escape the regex as you need, in some languages, you will have to add one more "\".

注意：根据需要转义正则表达式，在某些语言中，您必须再添加一个“\”。

In my Postgres DB, name column contains following, when I use simple order by name query:

在我的 Postgres 数据库中，当我使用按名称查询的简单顺序时，名称列包含以下内容：

• 1
• 10
• 2
• 21
• A
• A1
• A11
• A5
• B
• B2
• B22
• B3
• M 1
• M 11
• M 2

• 1
• 10
• 2
• 21
• 一个
• A1
• A11
• A5
• 乙
• B2
• B22
• B3
• 米 1
• 米11
• 米 2

Result of Query, After I have modified it:

查询结果，修改后：

• 1
• 2
• 10
• 21
• A
• A1
• A5
• A11
• B
• B2
• B3
• B22
• M 1
• M 2
• M 11

• 1
• 2
• 10
• 21
• 一个
• A1
• A5
• A11
• 乙
• B2
• B3
• B22
• 米 1
• 米 2
• 米11

回答by oupoup

A Vlk's answer above helped me a lot, but it sorted items only by the numeric part, which in my case came second. My data was like (desk 1, desk 2, desk 3 ...) a string part, a space and a numeric part. The syntax in A Vlk's answer returned the data sorted by the number, and at that it was the only answer from the above that did the trick. However when the string part was different, (eg desk 3, desk 4, table 1, desk 5...) table 1 would get first from desk 2. I fixed this using the syntax below:

上面 Vlk 的回答对我有很大帮助，但它仅按数字部分对项目进行排序，在我的情况下，数字部分排在第二位。我的数据就像（桌子 1、桌子 2、桌子 3 ...）一个字符串部分、一个空格和一个数字部分。A Vlk 的答案中的语法返回了按数字排序的数据，这是上面唯一能解决问题的答案。但是，当字符串部分不同时，（例如，桌子 3、桌子 4、桌子 1、桌子 5...）桌子 1 将首先从桌子 2 获取。我使用以下语法修复了这个问题：

    ...order by SUBSTRING(name,'\w+'), SUBSTRINGname FROM '([0-9]+)')::BIGINT ASC;

回答by no1uknow

Tor's last SQL worked for me. However if you are calling this code from php you need add extra slashes.

Tor 的最后一条 SQL 对我有用。但是，如果您从 php 调用此代码，则需要添加额外的斜杠。

SELECT name
FROM folders
ORDER BY
COALESCE(SUBSTRING(name FROM '^(\\d+)')::INTEGER, 99999999),
SUBSTRING(name FROM '^\\d* *(.*?)( \\d+)?$'),
COALESCE(SUBSTRING(name FROM ' (\\d+)$')::INTEGER, 0),
name;