从PostgreSQL字段中提取数字

Question

我在Postgres 8.4中有一张包含一个varchar类型的po_number列的表格。它存储带有一些特殊字符的字母数字值。我想忽略[/alpha/?/$/encoding/.]字符并检查该列是否包含数字。如果是数字，则需要将其强制转换为数字，否则传递null，因为我的输出字段po_number_new是数字字段。

以下是示例：

SQL Fiddle。

我尝试了这个语句:

```sql SELECT po_number, CASE WHEN regexp_replace(po_number, '[^0-9]', '', 'g') = '' THEN null ELSE regexp_replace(po_number, '[^0-9]', '', 'g')::NUMERIC END AS po_number_new FROM my_table; ```

select 
(case when  regexp_replace(po_number,'[^\w],.-+\?/','') then po_number::numeric
else null
end) as po_number_new from test

但是我在进行显式转换时出现了错误：

- user1538020

---

恢复您的新问题，您将得到答案。只需仔细解释所有边缘情况即可。 - Tim Biegeleisen

没问题，蒂姆。我以后会发布一个完整的问题而不做任何编辑：）没有问题。顺便说一下，谢谢：） - user1538020

Answer 1

简单来说：

SELECT NULLIF(regexp_replace(po_number, '\D','','g'), '')::numeric AS result
FROM   tbl;

\D是表示“非数字”的类的简写。需要第四个参数'g'（代表“全局”）来替换所有出现的字符。详情请参阅手册。

对于已知、有限的一组要替换的字符，使用普通的字符串操作函数，如replace()或translate()会更加便宜。正则表达式更加灵活，我们在本例中要消除除数字以外的所有字符。相关链接：

• 正则表达式删除字符串中多个字符的所有实例
• PostgreSQL仅选择行中的字母字符
• 是否有适用于PostgreSQL 7.4的regexp_replace等效函数？

但为什么使用Postgres 8.4？考虑升级到现代版本。

请考虑过时版本的陷阱：

• 将varchar字符串按数字排序
• 警告：字符串字面值中使用了非标准转义字符

- Erwin Brandstetter

---

你可能会对我提出的解决方案感兴趣——在这种特别简单的情况下，使用“TRANSLATE”函数而不是更昂贵的“REGEXP_REPLACE”？我对我所写的任何评论都很感兴趣和感激！我来到这里的原因是这个问题——非常棘手！ :-) 我已经为PostgreSQL有一个可行的解决方案，而我正在聊天的人则提出了一个SQL Server的解决方案——尝试在没有正则表达式的情况下做到这一点是棘手的！ - Vérace

Answer 2

如果你想提取浮点数，请尝试使用以下方法：

SELECT NULLIF(regexp_replace(po_number, '[^\.\d]','','g'), '')::numeric AS result FROM tbl;

这个和Erwin Brandstetter的答案相同，只是表述不同：

[^...] - 匹配除了排除列表中的字符以外的任何字符，将被排除的字符放在...的位置

\. - 点字符（也可以改成逗号,）

\d - 数字字符

- doninpr

Answer 3

我认为你想要的是这样的东西：

select (case when regexp_replace(po_number, '[^\w],.-+\?/', '') ~ '^[0-9]+$'
             then regexp_replace(po_number, '[^\w],.-+\?/', '')::numeric
        end) as po_number_new 
from test;

也就是说，在替换后，您需要对字符串进行转换。

注意：这假设“数字”只是一串数字。

- Gordon Linoff

---

你能解释一下正则表达式,.-+的含义吗？ - Abelisto

@Abelisto 我认为它们应该在括号内，尽管我现在不在Postgre面前进行测试。 - Tim Biegeleisen

使用上述方法后，我只得到了空值...相反，我需要每个记录中的数字和没有数字的记录的空值。 - user1538020

Answer 4

自版本12以来 - 在撰写本文时已经过去了2年+4个月（但在我看到的已接受答案的最后一次编辑之后），您可以使用GENERATED FIELD轻松地进行一次性操作，而不必每次希望SELECT一个新的po_number时都要计算它。

此外，您可以使用TRANSLATE函数提取数字，这比@ErwinBrandstetter提出的REGEXP_REPLACE解决方案更节省成本！

我会按照以下方式执行此操作（下面的所有代码都可以在fiddle here上找到）：

CREATE TABLE s
(
  num TEXT,
  
  new_num INTEGER GENERATED ALWAYS AS
    (NULLIF(TRANSLATE(num, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ. ', ''), '')::INTEGER) STORED
);

您好，这段文字的英译中翻译如下：

您可以根据需要在TRANSLATE函数中添加'ABCDEFG...'字符串 - 我在末尾加了小数点（.）和空格（ ）- 根据您的输入，您可能希望在那里添加更多字符！

并进行检查：

INSERT INTO s VALUES ('2'), (''), (NULL), (' ');
INSERT INTO t VALUES ('2'), (''), (NULL), (' ');
SELECT * FROM s;
SELECT * FROM t;

结果（两者相同）：

num    new_num
  2          2
          NULL
          NULL
          NULL

因此，我想检查我的解决方案有多有效率，所以我运行了以下测试，将10,000条记录插入到表 s 和 t 中，如下所示（来自此处）：

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
INSERT INTO t 
with symbols(characters) as 
(
  VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')
)
select string_agg(substr(characters, (random() * length(characters) + 1) :: INTEGER, 1), '')
from symbols
join generate_series(1,10) as word(chr_idx) on 1 = 1 -- word length
join generate_series(1,10000) as words(idx) on 1 = 1 -- # of words
group by idx;

差异并不是很大，但是使用正则表达式的解决方案始终比使用INSERT语句更慢约25％，即使改变进行INSERT的表格顺序也是如此。

然而，当执行“原始”SELECT时，TRANSLATE解决方案真正发挥作用，如下所示：

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
  NULLIF(TRANSLATE(num, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ. ', ''), '')::INTEGER
FROM s;

“

对于REGEXP_REPLACE解决方案也是一样。

差异非常明显，TRANSLATE函数的时间大约是另一个函数的25%。最后，为了公正起见，我也对这两个表格都进行了测试：

”

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
  num, new_num
FROM t;

"非常快速且完全相同！"

- Vérace

---

是的，正则表达式函数被认为是相当昂贵的（在最近的版本中变得更快了，但仍然如此）。在过去的十年中，我一直在指出这一点：https://dev59.com/U4fca4cB1Zd3GeqPgkQH#28172693，https://stackoverflow.com/a/34106732/939860，...然而，在ASCII时代列出所有可能的非数字要实用得多，而在UTF时代就不那么实用了... - Erwin Brandstetter

Answer 5

我会使用以下逻辑来确定po_number字段是否包含数字：当尝试去除数字时，其长度应该减少。

如果是这样，那么po_number列中的所有非数字字符([^\d])都应该被删除。否则，应返回NULL。

select case when char_length(regexp_replace(po_number, '\d', '', 'g')) < char_length(po_number)
            then regexp_replace(po_number, '[^0-9]', '', 'g')
            else null
       end as po_number_new
from test

- Tim Biegeleisen

---

我在尝试后遇到了一个 SQL 错误 [42883]。 - user1538020

@user1538020，错误是由于您使用的是Postgres 8.x引起的，该版本没有length函数。我更新为使用char_length，现在应该可以工作了。 - Tim Biegeleisen

我执行了 select version(); 命令，现在正在使用由 Visual C++ build 1800 编译的 PostgreSQL 9.5.2 64 位版本。但是仍然出现错误。我已经发布了错误图片。 - user1538020

@user1538020: 附带说明：你看到的错误是由于打字错误引起的: regex_replace<> regexp_replace。而且在pg 8.4中确实有一个 length() 函数。 - Erwin Brandstetter

@Tim Biegeleisen：选择当通过正则表达式替换(po_number, '\d', '', 'g')的字符长度小于po_number的字符长度时， 则将po_number中的非数字字符替换为空 否则返回null 作为po_number_new 从test表中查询；语法已修正，现在可以运行了。谢谢。 - user1538020

@ErwinBrandstetter 谢谢Erwin，你真的是Stack Overflow上Postgres之王 :-) - Tim Biegeleisen