Pulling my hair with this. Any help or direction would be very much appreciated.
Column values:
u-165645,c-934846,f-598715,one, appple
c-997556.41,test,u-4404932, testing,two
Trying to extract values that begins with 'c-'. From above examples, the extracted would be:
934846
997556.41
Give this a try. The WITH clause just sets up your data using a Common Table Expression (CTE). The regular expression uses a captured group to return what follows the "c-" when followed by a comma or the end of the line, so it does not matter where in the string the pattern occurs.
WITH tbl(ID, DATA) AS (
SELECT 1, 'u-165645,c-934846,f-598715,one, appple' FROM dual UNION ALL
SELECT 2, 'c-997556.41,test,u-4404932, testing,two' FROM dual
)
SELECT ID,
REGEXP_SUBSTR(DATA, 'c-(.*?)(,|$)', 1, 1, NULL, 1) c_element
FROM tbl;
ID C_ELEMENT
---------- ---------------------------------------
1 934846
2 997556.41
2 rows selected.
You capture:
c-([*]*)
and replace by
\1
I don't know which language or editor you are using... I've tried this on Notepad++
