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
233141
233141
233142
233147
233148
But I want to the result as below, what can I do?
233141,233141,233142,233147,233148
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
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!!
233141233141233142233147233148, that is not what I need:)
listagg(regexp_substr(blabla), ', ') within group ...