在PostgreSQL中如何获取字符串中所有子字符串的实例？

Question

我有一个类似于“something XXX010101 somethingelse XXX010102”的条目表格。

我想要从中提取XXX01...部分 - 如果需要，可能使用一些分隔符。目前，我可以轻松地使用以下方法取出第一个部分：

select distinct substring(content from 'XXX[0-9]+'), content from data where content ~ 'XXX[0-9]+'

我唯一的想法是创建一些巨大的正则表达式来替换不是XXX子字符串的所有内容...但实际上，我希望（并未找到）有一个更简单的解决方案（例如一个g标志来子字符串）。当我四处寻找时，我发现8.3引入了一个regex_matches函数，似乎是我需要的-在8.2中是否有任何替代方法？或者有没有简单的方法在8.2中获得它？你会如何解决这个问题？还是升级是最好的选择？ ;)（它是一个生产系统，因此迁移后的停机时间和一些问题的风险是犹豫的来源）。预先感谢。--添加预期输出--

"something XXX010101 something else XXX010102"

我希望获得：

XXX010101
XXX010102

或（不太理想）

XXX010101,XXX010102 

- RnR

---

你知道8.2即将停止支持吗？ - user330315

我之前不知道，谢谢提醒。我们从未遇到过任何问题，因此支持并不是一个真正的问题，我计划在我们有停机时间“无论如何”进行更新，出于其他原因（数据中心迁移等（计划在几个月内完成））。 - RnR

De-Supported 意味着从明年开始将不再提供任何错误修复（安全或其他）。请参见此处：http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy。 - user330315

发布政策已经移至官方网站：http://www.postgresql.org/support/versioning/。 - Erwin Brandstetter

Answer 1

我有些犹豫是否要发布我的答案。你真的需要升级。版本8.2现在已经到了生命周期的尽头。请点击@a_horse_with_no_name发布的链接。

然而，这个问题引起了我的注意。以下演示应该可以在PostgreSQL 8.2上正常运行：

SELECT -- content,
         rtrim(
         regexp_replace(
         regexp_replace(
         replace(
         substring(content
        ,E'(XXX\\d+|XXX\\d+.*XXX\\d+)')  -- kill leading / trailing noise
        ,',',' ')                        -- remove all ","
        ,E'(XXX\\d+)', E'\\1,', 'g')     -- terminate X-strings with ","
        -- now we can use non-greedy terminated with ","
        ,E'(XXX\\d+?,)*?.*?(XXX\\d+?,)', E'\\1\\2', 'g')
        ,',') AS result
FROM    (VALUES
  ('no match')
 ,('XXX010101')
 ,('XXX010102 beginn')
 ,('end XXX010103')
 ,('middle XXX010104 match')
 ,('match middle XXX010105 and end XXX010106')
 ,('XXX010107, match beginn XXX010108 & middle')
 ,('XXX010109 begin and end XXX010110')
 ,('XXX01011 begin XXX010112 middle and end XXX010113')
 ,('XXX01014 begin XXX010115 close matches XX010113 XXXy010113 23624 ,XXX010116')
 ,('f XXX01017 B XXX010118 457XXX010119 XXXy XXX010120 overkill XXX010121end')
) data(content)

结果：

                     result
--------------------------------------------------
             -- first line is NULL
 XXX010101
 XXX010102
 XXX010103
 XXX010104
 XXX010105,XXX010106
 XXX010107,XXX010108
 XXX010109,XXX010110
 XXX01011,XXX010112,XXX010113
 XXX01014,XXX010115,XXX010116
 XXX01017,XXX010118,XXX010119,XXX010120,XXX010121

一些解释:

• 正如OP所说，版本8.2中没有regex_matches()
• 但是有regexp_replace()可以使用g标志(全局替换)
• 我们不能在同一个正则表达式中混合贪婪和非贪婪量词
• 因此，我用,终止需要的字符串，在删除所有其他出现之后。任何不属于需要的字符串的字符都可以，但是,可以作为结果中的分隔符。
• 首先剪切前导和尾随噪音
• 然后全局替换需要的字符串之间的所有内容。
• 为了使其工作，使用(XXX\\d+?,)*?来捕获任意数量的需要字符串的前导出现。

• 最后的rtrim()删除尾随的,

• 在PsotgreSQL 8.3+中，您可以使用regexp_split_to_table()将需要的字符串拆分为单行。在8.2中，您必须自己想办法。我会写一个plgpsql函数...

这充分利用了PostgreSQL的POSIX正则表达式的特性（链接到版本8.2！）

- Erwin Brandstetter

Answer 2

如果您要查找的值包含在单独的表中，可以尝试类似这样的方法...但是我不能保证它的性能。

Select A.text, B.Text2, B.Val 
FROM A 
INNER JOIN B ON B.Text2 LIKE ('%' || A.Text || '%')

让表A包含所有你要寻找的XXX010101...的可能组合。

text
XXX010101
XXX010102
XXX010103

让表B包含您想要搜索的所有文本

text 2                                              val
something XXX010101 something else XXX010102        1
yet another XXX010102 and this XXX010103            2
XXX010105                                           3
XXX010103                                           4

结果：

text            text2                                           VAL
XXX010101   something XXX010101 something else XXX010102    1
XXX010102   something XXX010101 something else XXX010102    1
XXX010102   yet another XXX010102 and this XXX010103        2
XXX010103   yet another XXX010102 and this XXX010103        2
XXX010103   XXX010103                                       4

替换函数有什么问题吗？ http://www.postgresql.org/docs/8.2/interactive/functions-string.html

replace( 'abcdefabcdef', 'cd', '')

所以字符被设置为空字符串。

- xQbert

---

说实话，主要是正则表达式很难读懂并且“逻辑上反转”，而且我需要在客户端解析输出 - 在这种情况下，我可以完全在客户端完成这项工作，而且我认为这样更易读？（while ($content =~ m/XXX[0-9]{6}/g) ... ）。 - RnR

我想我不理解问题。你能更新一下问题，包括你想要的样例输入/输出吗？ - xQbert

是的，我完全误解了问题:D - xQbert

有没有一个表格包含所有可能的XXX010101，XXX010102...的值？ - xQbert

Answer 3

最快的方法是使用 plperlu，它可以与8.2版本配合使用。

CREATE LANGUAGE plperl

CREATE FUNCTION get_things(inputStr text)
RETURNS SETOF text
AS $BODY$
  return \@{[ $_[0] =~ m/(XXX\d{6})/g ]} 
$BODY$
LANGUAGE plperl
IMMUTABLE;

SELECT get_things(x)
FROM ( VALUES
  ('XXX010101 somethingelse XXX010102')
) AS t(x);

 get_things 
------------
 XXX010101
 XXX010102
(2 rows)

它也适用于新版本的PostgreSQL。

- Evan Carroll