I have a query which fetches value matching the pattern, I want it to fetch all prefvalue instead of only CEROTG
SELECT regexp_substr('prefvalue:CEROTG-2 prefvalue:CEROTG prefvalue:CEROTG_1', 'prefvalue:([[:alnum:]_]+)') as Result from dual
Current Output
prefvalue:CEROTG
Expected Output
prefvalue:CEROTG
prefvalue:CEROTG-2
prefvalue:CEROTG_1
One option uses a recursive query:
with
data as (select 'prefvalue:CEROTG-2 prefvalue:CEROTG prefvalue:CEROTG_1' str from dual),
cte(str, res, lvl) as (
select str, regexp_substr(str, 'prefvalue:([[:alnum:]_-]+)'), 1 lvl from data
union all
select str, regexp_substr(str, 'prefvalue:([[:alnum:]_-]+)', 1, lvl + 1), lvl + 1
from cte
where lvl < regexp_count(str, 'prefvalue:([[:alnum:]_-]+)')
)
select res from cte
| RES | | :----------------- | | prefvalue:CEROTG-2 | | prefvalue:CEROTG | | prefvalue:CEROTG_1 |
connect by, which are the same in essence). You can move your existing query to the data cte.You can use simple string functions in a recursive subquery-factoring clause:
WITH rsqfc ( entry_data, start_pos, end_pos ) AS (
SELECT entry_data, 1, INSTR( entry_data, ' ', 1 )
FROM prefdir_entrydata
UNION ALL
SELECT entry_data, end_pos + 1, INSTR( entry_data, ' ', end_pos + 1 )
FROM rsqfc
WHERE end_pos > 0
)
SELECT CASE end_pos
WHEN 0
THEN SUBSTR( entry_data, start_pos )
ELSE SUBSTR( entry_data, start_pos, end_pos - start_pos )
END AS value
FROM rsqfc
Which, for your sample data:
CREATE TABLE prefdir_entrydata ( entry_data ) AS
SELECT 'prefvalue:CEROTG-2 prefvalue:CEROTG prefvalue:CEROTG_1' FROM DUAL;
Outputs:
| VALUE | | :----------------- | | prefvalue:CEROTG-2 | | prefvalue:CEROTG | | prefvalue:CEROTG_1 |
db<>fiddle here
An example that handles multiple input rows is:
WITH rsqfc ( id, entry_data, start_pos, end_pos ) AS (
SELECT id, entry_data, 1, INSTR( entry_data, ' ', 1 )
FROM prefdir_entrydata
WHERE dist_name_short = 'prefentry=imagerepository,prefgroup=cdi_globals,prefgroup=component,prefgroup=system,prefcontext=default,prefroot=prefroot'
UNION ALL
SELECT id, entry_data, end_pos + 1, INSTR( entry_data, ' ', end_pos + 1 )
FROM rsqfc
WHERE end_pos > 0
)
SELECT id,
CASE end_pos
WHEN 0
THEN SUBSTR( entry_data, start_pos )
ELSE SUBSTR( entry_data, start_pos, end_pos - start_pos )
END AS value
FROM rsqfc
ORDER BY id, start_pos
Which, for the test data:
CREATE TABLE prefdir_entrydata ( id, entry_data, dist_name_short ) AS
SELECT 1,
'prefvalue:CEROTG-2 prefvalue:CEROTG prefvalue:CEROTG_1',
'prefentry=imagerepository,prefgroup=cdi_globals,prefgroup=component,prefgroup=system,prefcontext=default,prefroot=prefroot'
FROM DUAL UNION ALL
SELECT 2,
'prefvalue:CEROTG-2a prefvalue:CEROTG_1v2',
'prefentry=imagerepository,prefgroup=cdi_globals,prefgroup=component,prefgroup=system,prefcontext=default,prefroot=prefroot'
FROM DUAL;
Outputs:
ID | VALUE -: | :------------------- 1 | prefvalue:CEROTG-2 1 | prefvalue:CEROTG 1 | prefvalue:CEROTG_1 2 | prefvalue:CEROTG-2a 2 | prefvalue:CEROTG_1v2
db<>fiddle here
WITH tbl(DATA) AS (
SELECT 'prefvalue:CEROTG-2 prefvalue:CEROTG prefvalue:CEROTG_1'
FROM dual
)
SELECT REGEXP_SUBSTR(DATA, '(.*?)( |$)', 1, LEVEL, NULL, 1) ELEMENT
FROM tbl
CONNECT BY LEVEL <= REGEXP_COUNT(DATA, ' ')+1;
ELEMENT
------------------------------------------------------
prefvalue:CEROTG-2
prefvalue:CEROTG
prefvalue:CEROTG_1
3 rows selected.
