在regexp_replace中使用lpad [英] Using lpad in regexp_replace

问题描述

我正在使用PostgreSQL 9.5，并且有一个带有 area_name文本列且具有编号扩展名的表：

I am Using PostgreSQL 9.5 and have a table with a "area_name" text column of names that have numbered extentions:

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

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

I want to order the result numerically by extention as shown above.

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

I have tried using a regexp_replace to lpad the numbers with 0s, but using a length of 4 is adding 2 0s in front of the number regardless of if it is 1 or 2 digits!

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

替换表达式在哪里出问题？

Where am I going wrong with the replace expression?

推荐答案

我不确定您为什么要格式化问题以进行排序。那很简单。只需将它们保留为 int 。

I'm not sure why you're trying to format them your question calls for ordering. That's pretty simple. Just keep them as int.

我要订购结果

只需将 ORDER BY 语句。

SELECT area_name
FROM ext_test
ORDER BY
  CASE
    WHEN area_name ~ '\d'
    THEN (regexp_matches(area_name, '\d+'))[1]::int
  END NULLS FIRST,
  area_name;

  area_name  
-------------
 AREA
 AREA EXT
 AREA EXT 1
 AREA EXT 5
 AREA EXT 49
 AREA EXT 50
(6 rows)

这篇关于在regexp_replace中使用lpad的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！