排序時如何將字元串中的數字視為數字（“A3”在“A10”之前排序，而不是在之後）

對於所有這些查詢：

SELECT label FROM personal.storage_disks ORDER BY label ASC;
SELECT label FROM personal.storage_disks ORDER BY label COLLATE "C" ASC;
SELECT label FROM personal.storage_disks ORDER BY label COLLATE "POSIX" ASC;
SELECT label FROM personal.storage_disks ORDER BY label COLLATE "default" ASC;

輸出總是：DISK 1, DISK 10, DISK 2, DISK 3, [...]

但是，我想要並期望：DISK 1, DISK 2, DISK 3, [...] DISK 10

我現在沒有排序規則可以嘗試根據SELECT * FROM pg_collation;……除非我應該使用許多非常奇怪的帶有神秘名稱的排序規則之一。（我什至嘗試了一堆具有相同結果的方法。）

請注意，我已經閱讀了現有的看似相關的 SE 問題以及關於 的許多文章SORT BY，但它們沒有幫助，也沒有為我解決任何問題。

我正在使用 PostgreSQL 12.4

對字元串進行排序自然會將“15”放在“2”之前，因為“15”中的第一個數字是“1”，它排在“2”之前。可以通過幾種方式在“15”之前對儲存在字元串類型中的“2”進行排序。最有效的方法是讓排序規則本身在內部處理這個問題。這個選項並不為人所知，甚至在大多數地方都不可用，但是任何實現ICU（Unicode 的國際組件）******的系統都有可能允許這種類型的排序（只要它允許自定義排序選項），這通常被稱為“自然”排序。

處理自然排序通常是通過將字元串切成純字母和數字片段，然後單獨排序來以程式方式完成的。在許多情況下，這是必要的邪惡，但幸運的是，PostgreSQL（至少從版本 10 開始）在內部確實允許這樣做。您需要創建一個自定義排序規則（這個排序規則甚至直接來自他們的文件）：

CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true');

並在ORDER BY. 這是它的一個工作範例：

https://dbfiddle.uk/?rdbms=postgres_11&fiddle=58763b51a8ccb2360cf387d8c2b91d51

筆記

由於排序規則通常沒有被很好地理解，並且自定義它們的能力更新（至少對於數據庫）甚至更深奧，我建議在實施此解決方案時執行以下操作：

1. 用“custom_”為新排序規則的名稱添加前綴，以提高對這確實是一個自定義排序規則的認識，該排序規則可能具有不明顯的行為並且可能不存在於其他系統上（因此可能需要添加到系統或應用程序設置過程中）
2. 在使用此自定義排序規則的每個查詢之後添加註釋，指出它是自定義排序規則，並包含指向官方文件的連結：

https ://www.postgresql.org/docs/12/collat​​ion.html#id -1.6.10.4.5.7.5

您甚至可以提到該-kn-true部分啟用“數字”排序。

獎金

為了更全面地展示“數字”排序選項的工作原理，我在前面的範例中添加了一些數據以顯示：

• 字元串中的多個/單獨的數字組分別處理
• 不同的非數字字元按預期處理
• 前導 0 不影響結果

附加數據是：

DISK 2A
DISK 2B
DISK 2B 33
DISK 2B 4
FILE 62
FILE 7
DIRECTORY 1000000
DIRECTORY 57
DIRECTORY 9999
DIRECTORY 57000
DIRECTORY 057
DIRECTORY 0057
DIRECTORY 52

這是更新的範例：

https://dbfiddle.uk/?rdbms=postgres_11&fiddle=20416b0dd731b2cc28b6fdee8ef70ec7

---

******公平地說，ICU / Unicode 不是“必需”進行這種類型的排序，因為任何排序規則或系統都可以實現相同的算法。但是，它是內置在 ICU 中的，越來越多的系統正在集成 ICU。

磁碟 1、磁碟 10、磁碟 2、磁碟 3

這不是您想要的，但它是排序文本時的正確順序。數字也是字母，它們按字母順序排序。用引號表示“這是文本文字”，確實是“10”<“2”。

我想要並期待：磁碟 1、磁碟 2、磁碟 3、

$$ … $$磁碟 10

這很常見。我認為 Windows 資源管理器可以做到這一點。

如果你想按字母順序排列它們，那麼一個快速的解決方案是像 ISO 日期一樣：‘Disk 01’ 確實在 ‘Disk 10’ 之前，因為按字母順序 ‘0’ < ‘1’…

如果您不想在標籤中出現前導零，那麼您需要創建一個可以即時添加它們的函式。你可以用 plpgsql 來做到這一點，或者混合一個正則表達式：

select regexp_replace(
   regexp_replace(column1, '(\d+)', '000000\1')
   , '0*(\d{4})', '\1' ) r 
FROM (VALUES ('Drive 1'),('Drive 10'),('Drive 2'),('Drive 100000') )v 
ORDER BY r;
     r
--------------
Drive 0001
Drive 0002
Drive 0010
Drive 100000

這會為所有數字添加固定數量的前導零，然後將它們修剪掉，使數字總數為 4。如果需要，您可以添加更多數字。最後一行顯示當原始數字有太多數字時它不再起作用，但希望您不會有超過一個 gorillion 驅動器來排序。正則表達式可能需要一些修飾。

如果這是python，可以將文本字元串轉換為元組或數組，例如

$$ ‘Drive’,10 $$其中整數是實際的整數，然後會正確排序。但是您不能在 postgres 數組中混合數據類型，因此是雜亂無章的。 如果你想使用它，那麼把上面的 regexp_replace() 作為你的 ORDER BY 參數，不需要把它放在選擇列表中，這只是為了舉例，並顯示它的結果。

引用自：https://dba.stackexchange.com/questions/285222