postgresql数字结尾字符串分割排序说明

功能说明：对以数字结尾的字符串进行分割之后，第一部分按照postgresql默认的排序方式排序，第二部分按照数字的大小进行排序。 目标排序结果，以数字结尾字符串前半部分(addd-)一样情况下按照后半部分数字（1、2、3、10）大小排序。 相关自定义函数： ### PostgreSQL数字结尾字符串分割排序详解 #### 功能概述 本文档旨在详细介绍如何通过自定义函数在PostgreSQL数据库中实现一种特殊的排序方式：对于以数字结尾的字符串，首先将其分为两部分，一部分为非数字字符串，另一部分为数字；然后根据这两部分分别进行排序。具体而言，非数字字符串部分按PostgreSQL的默认排序规则排序，而数字部分则按数值大小排序。 #### 排序需求 在某些应用场景下，可能需要对特定格式的数据进行特殊排序处理。例如，对于以`addd-`开头并以数字结尾的字符串（如`addd-1`, `addd-2`, `addd-3`, `addd-10`），通常希望它们能够按照字符串前缀的默认排序以及数字的自然顺序进行排序。即，当字符串前缀相同时，后续的数字应按照升序排列。 #### 实现方法 为了实现这一排序需求，可以通过编写两个自定义函数来完成：`subnamenum`用于提取字符串中的数字部分，`subnamestr`用于提取字符串中的非数字部分。接着，在查询语句中使用这两个函数来进行排序。 #### 自定义函数介绍 ##### 函数 `subnamenum` **函数结构：** ```sql CREATE OR REPLACE FUNCTION "subnamenum"(name varchar) RETURNS "pg_catalog"."int8" AS $BODY$ BEGIN RETURN to_number(substring(name from '[0-9]+$'), '999999999999999999999999999'); END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100 ; ``` **功能说明：** 此函数接收一个`varchar`类型的参数`name`，并返回该字符串末尾数字的部分作为整型值。这里的正则表达式`[0-9]+$`表示匹配字符串末尾的一个或多个数字。 **示例：** 假设有一个字符串`addd-10`，则`subnamenum('addd-10')`将返回整数`10`。 ##### 函数 `subnamestr` **函数结构：** ```sql CREATE OR REPLACE FUNCTION "subnamestr"(name varchar) RETURNS "pg_catalog"."varchar" AS $BODY$ BEGIN RETURN coalesce(SUBSTRING(name from 0 FOR length(name) - length(SUBSTRING(name FROM '[0-9]+$')) + 1), name); END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100 ; ``` **功能说明：** 该函数同样接收一个`varchar`类型的参数`name`，返回除了字符串末尾数字以外的其他部分。这里利用了`SUBSTRING`函数与`length`函数结合正则表达式来提取非数字部分。 **示例：** 如果字符串为`addd-10`，那么`subnamestr('addd-10')`将返回`addd-`。 #### 函数调用示例 在实际应用中，可以采用两种方式调用这些自定义函数进行排序： 1. **HQL（Hibernate Query Language）调用**： ```sql SELECT * FROM table as model ORDER BY subnamestr(model.name), subnamenum(model.name); ``` 2. **SQL调用**： ```sql SELECT * FROM table as model ORDER BY subnamestr(model.name), subnamenum(model.name); ``` #### 排序示例 假设有一个名为`items`的表，其中包含以下数据： | name | |--------------| | addd-1 | | addd-10 | | addd-2 | | addd-3 | | addd-11 | **默认排序结果：** 按照PostgreSQL默认排序规则，排序结果如下： 1. addd-1 2. addd-10 3. addd-11 4. addd-2 5. addd-3 **目标排序结果：** 使用上述自定义函数进行排序后，结果如下： 1. addd-1 2. addd-2 3. addd-3 4. addd-10 5. addd-11 通过这种方式，我们成功实现了对于以数字结尾的字符串的特殊排序需求。这种排序方式不仅可以提高数据展示的合理性，还能帮助开发者更好地管理和操作数据。