postgresql - 在 regexp_replace 中使用 lpad

Question

我正在使用 PostgreSQL 9.5 并且有一个表，其中包含名称的“area_name”文本列，这些名称具有编号扩展名:

area_name
----------------
AREA
AREA EXT
AREA EXT 1
AREA EXT 5
AREA EXT 49
AREA EXT 50

我想按扩展名对结果进行数字排序，如上所示。

我曾尝试使用 regexp_replace 将数字填充为 0，但是使用长度为 4 的数字会在数字前面添加 2 个 0，无论它是 1 位还是 2 位数字!

create table ext_test (
    area_name text
);

insert into ext_test values
  ('AREA'),
  ('AREA EXT'),
  ('AREA EXT 1'),
  ('AREA EXT 5'),
  ('AREA EXT 49'),
  ('AREA EXT 50');

select
    area_name,
    regexp_replace(area_name, ' EXT (\d*)', ' EXT ' || lpad('\1', 4, '0')) as order_result
from ext_test
order by order_result;

area_name    | order_result
------------------------------ 
AREA         | AREA
AREA EXT     | AREA EXT
AREA EXT 1   | AREA EXT 001
AREA EXT 49  | AREA EXT 0049
AREA EXT 5   | AREA EXT 005
AREA EXT 50  | AREA EXT 0050

替换表达式哪里出错了？

Answer 1

当你执行 regexp_replace(area_name, 'EXT (\d*)', 'EXT' || lpad('\1', 4, '0'))
首先正在评估所有参数

lpad('\1', 4, '0') 变成 '00\1'

'EXT' || lpad('\1', 4, '0') 变成 ' EXT 00\1'

这意味着捕获的组(在本例中为数字)将以 2 个零开头。

---

您的目标可以分两个阶段实现 -

1. 用 X 前面的零填充所有数字
2. 将每个数字截断到最右边的 X 位

---

select      area_name
           ,regexp_replace(regexp_replace(area_name,'\d+',repeat('0',4) || '\&'),'\d*(\d{4})','\1') as order_result

from        ext_test

order by    order_result
;

---

+-------------+---------------+
| area_name   | order_result  |
+-------------+---------------+
| AREA        | AREA          |
+-------------+---------------+
| AREA EXT    | AREA EXT      |
+-------------+---------------+
| AREA EXT 1  | AREA EXT 0001 |
+-------------+---------------+
| AREA EXT 5  | AREA EXT 0005 |
+-------------+---------------+
| AREA EXT 49 | AREA EXT 0049 |
+-------------+---------------+
| AREA EXT 50 | AREA EXT 0050 |
+-------------+---------------+

---

如果您的文本中有多个数字，请使用基于 'AREA EXT ' -

的版本

select      area_name
           ,regexp_replace(regexp_replace(area_name,'(?<=AREA EXT )\d+',repeat('0',4) || '\&'),'(?<=AREA EXT )\d*(\d{4})','\1') as order_result

from        ext_test

order by    order_result
;