如何在PostgreSQL中对不规则的字母数字数据进行排序 [英] How to do sorting on irregular Alphanumeric data in postgres sql

问题描述

对于示例表的特定列符号，我具有以下示例数据。

（更新：）数据不是常规模式。数字可以在字符之间的任何位置出现。

 符号
 
 COL4A1 
 COL4A3 
 COL8A2 
 COL2A1 
 COL12A1 
 COL12A1 
 COL16A1 
 COL19A1 
  

我需要在数据库级别对这些数据进行排序。我使用了以下查询：

 从示例订单中按符号asc 
  <选择符号/ pre> 
 
 
结果如下：
  COL12A1 
 COL12A1 
 COL16A1 
 COL19A1 
 COL2A1 
 COL4A1 
 COL4A3 
 COL8A2 
  
但是我需要通过以下方式获得订单：
  COL2A1 
 COL4A1 
 COL4A3 
 COL8A2 
 COL12A1 
 COL12A1 
 COL16A1 
 COL19A1 
  
 
 
解决方案
 PostgreSQL不提供可以识别 1A，2A，3A，... 10A等人性化排序的数字识别排序规则，11A，...。它依赖于操作系统进行排序规则，我不知道有任何操作系统可以将此类排序规则公开给应用程序。
 
 
 
为此，您需要拆分根据模式和按模式部分排列的文本，可能使用  regexp_matches  。
 创建表Table1（符号文本）； 
插入表1（符号）值
（'COL4A1'），（'COL4A3'），（'COL8A2'），（'COL2A1'），
（'COL12A1'） ，（'COL12A1'），（'COL16A1'），（'COL19A1'）; 
 
与matched（symbol，symbol_parts）AS（
 SELECT符号，regexp_matches（symbol，'（\D *）（\d +）（\D +）（\d +） '）
从Table1 
）
选择符号
 FROM匹配的
 ORDER BY symbol_parts [1]，symbol_parts [2] :: integer，
 symbol_parts [ 3]，symbol_parts [4] :: integer; 
  
 
I have the following sample data for a particular column symbol for sample table.

(Update:) The data is not in a regular pattern. Number may occur at any place in between characters. 
symbol

COL4A1
COL4A3
COL8A2
COL2A1
COL12A1
COL12A1
COL16A1
COL19A1
I need to sort the this data on database level. I used the following query:
select symbol from sample order by symbol asc
Result is follows:
COL12A1
COL12A1
COL16A1
COL19A1
COL2A1
COL4A1
COL4A3
COL8A2
But I need to get the order in the following way:
COL2A1
COL4A1
COL4A3
COL8A2
COL12A1
COL12A1
COL16A1
COL19A1

解决方案
PostgreSQL doesn't offer a number-aware collation that can do "humanized" sorts like "1A, 2A, 3A, ... 10A, 11A, ...". It relies on the operating system for collation, and I'm not aware of any OS that exposes such a collation to applications.

To do this, you need to split the text according to a pattern and order by the pattern parts, probably using regexp_matches.
CREATE TABLE Table1 ("symbol" text);
INSERT INTO Table1 ("symbol") VALUES
    ('COL4A1'),('COL4A3'),('COL8A2'),('COL2A1'),
    ('COL12A1'),('COL12A1'),('COL16A1'),('COL19A1');

WITH matched(symbol, symbol_parts) AS (
  SELECT symbol, regexp_matches(symbol, '(\D*)(\d+)(\D+)(\d+)')
  FROM Table1
)
SELECT symbol 
FROM matched
ORDER BY symbol_parts[1], symbol_parts[2]::integer,
         symbol_parts[3], symbol_parts[4]::integer;


                        
这篇关于如何在PostgreSQL中对不规则的字母数字数据进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！