Oracle to select all matched occurrences by a regular expression to one column in one row with separator

Question

I know to select all matched occurrences by a regular expression to one column in multiple rows as below:

SELECT REGEXP_SUBSTR(str, '\+?\d{3,11}', 1, LEVEL) AS substr
FROM (
    SELECT 'Txa233141b Ta233141 Ta233142 Ta233147 Ta233148' AS str FROM DUAL
)
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(str, '[^ ]+')) + 1;

the result is

But I want to the result as below, what can I do?

233141,233141,233142,233147,233148

score 2 · Accepted Answer · answered Sep 04 '19 at 06:43

Use simple string functions rather than trying to split the string and the aggregate it:

Query:

WITH test_data ( str ) AS (
  SELECT 'Txa233141b Ta233141 Ta233142 Ta233147 Ta233148' AS str FROM DUAL
)
SELECT TRIM(
         TRAILING ',' FROM
         REGEXP_REPLACE(
           str,
           '.*?(\+?\d{3,11}|$)',
           '\1,'
         )
       ) AS replaced_str
FROM   test_data

Output:

| REPLACED_STR                       |
| :--------------------------------- |
| 233141,233141,233142,233147,233148 |

db<>fiddle here

Popeye · Answer 2 · 2019-09-04T05:42:59.167

1

You can also use listagg on top of your query as following:

SELECT listagg(REGEXP_SUBSTR(str, '\+?\d{3,11}', 1, LEVEL),',') within group (order by 1) AS substr
FROM (
    SELECT 'Txa233141b Ta233141 Ta233142 Ta233147 Ta233148' AS str FROM DUAL
)
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(str, '[^ ]+')) + 1;

Cheers!!

edited Sep 04 '19 at 05:42

answered Sep 04 '19 at 04:07

Popeye

35,427
4
10
31

Hi, the result is `233141233141233142233147233148`, that is not what I need:) – mikezang Sep 04 '19 at 05:03
1

Set LISTAGG's second parameter to a comma, e.g. `listagg(regexp_substr(blabla), ', ') within group ...` – Littlefoot Sep 04 '19 at 05:12
Correct @littlefoot – Popeye Sep 04 '19 at 05:42

Oracle to select all matched occurrences by a regular expression to one column in one row with separator

2 Answers2