如何使用正则表达式获取多个匹配字符串

问题描述

我的字符串2711393|2711441|1234567

我的查询是:

SELECT REGEXP_SUBSTR('2711393|2711441|2711441', '([0-9]{7})') from DUAL;

实际输出为2711393.

预期输出为2711393, 2711441, 2711441.

推荐答案

如果您希望所有这些都作为 row 中的单个字符串，则无需使用正则表达式，您可以使用标准 REPLACE() :

If you want all of these as a single string in a row them there's no need to use regular expressions you can use a standard REPLACE():

SQL> select replace('2711393|2711441|1234567', '|', ', ') from dual;

REPLACE('2711393|2711441|
-------------------------
2711393, 2711441, 1234567

如果要将所有这些都放在一个列中，则需要使用 CONNECT BY ，因为我演示了.请注意，这是非常低效的.

If you want all of these in a single column then you need to use CONNECT BY as I demonstrate here. Please note that this is highly inefficient.

SQL>  select regexp_substr('2711393|2711441|1234567', '[^|]+', 1, level)
  2     from dual
  3  connect by regexp_substr('2711393|2711441|1234567'
  4                           , '[^|]+', 1, level) is not null;

REGEXP_SUBSTR('2711393|2711441|1234567','[^|]+',1,LEVEL)
--------------------------------------------------------------------------

2711393
2711441
1234567

SQL>

如果要在不同的列中使用它们，则需要使用 PIVOT ，您需要知道有多少个.我假设3.

If you want these in different columns you need to use PIVOT and you'll need to know how many you have. I'm assuming 3.

SQL> select *
  2    from (
  3   select regexp_substr('2711393|2711441|1234567', '[^|]+', 1, level) as a
  4        , level as lvl
  5     from dual
  6  connect by regexp_substr('2711393|2711441|1234567'
  7                           , '[^|]+', 1, level) is not null
  8          )
  9   pivot ( max(a)
 10          for lvl in (1,2,3)
 11          )
 12         ;

1          2          3
---------- ---------- ----------
2711393    2711441    1234567

SQL>

如您所见，这些都是完全可怕的，除了第一个，效率很低.您应该正确规范化数据库，以确保不必这样做.

As you can see these are all completely horrible and, save the first, highly inefficient. You should normalise your database correctly to ensure you don't have to do this.

这篇关于如何使用正则表达式获取多个匹配字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！