SQL Regular expression to split a column (string) to multiple rows based on delimiter '/n'
I have to split a column to multiple rows. The column stores string, and we have to split delimiter based on '/n'
Have written the below query. But not able to specify ^[/n]. The other 'n' in the string is also getting removed. Please help to parse the string
WITH sample AS
( SELECT 101 AS id,
'Name' test,
'3243243242342342/n12131212312/n123131232/n' as attribute_1,
'test value/nneenu not/nhoney' as attribute_2
FROM DUAL
)
-- end of sample data
SELECT id,
test,
regexp_substr(attribute_1,'[^/n]+', 1, column_value),
regexp_substr(attribute_2,'[^/]+', 1, column_value)
FROM sample,
TABLE(
CAST(
MULTISET(SELECT LEVEL
FROM dual
CONNECT BY LEVEL <= LENGTH(attribute_1) - LENGTH(replace(attribute_1, '/n')) + 1
) AS sys.OdciNumberList
)
)
WHERE regexp_substr(attribute_1,'[^/n]+', 1, column_value) IS NOT NULL
/