Regex to ignore delimiter for certain parts of the data

Current data and regex used

Consider the following data;

FDTYP/TESTTYPE/FDPERIOD/31 Day(s)/MAT_INST/CREDIT_TO_ACC/DEPTACCT/0016/

The current way used to separate the data is by using delimiter '/' to get this list of data;

{"FDTYP", "TESTTYPE", "FDPERIOD", "31 Day(s)", "MAT_INST", "CREDIT_TO_ACC", "DEPTACCT", "0016" }

and this is the code snippet with the regex used;

REGEXP_SUBSTR( ORGDATA ,'(.*?)/|$', 1, COLUMN_VALUE, NULL, 1 ) AS DATALABEL

The problem

Now consider this data

FDTYP/FD2323*/*/*-/FDPERIOD/31 Day(s)/MAT_INST/CREDIT_TO_ACC/DEPTACCT/0016/

The second data contains the delimiter '/' which produces the following list of data

{"FDTYP", "FD2323*", "*", "*-", "FDPERIOD", "31 Day(s)", "MAT_INST", "CREDIT_TO_ACC", "DEPTACCT", "0016" }

when the correct data should be

{"FDTYP", "FD2323*/*/*-", "FDPERIOD", "31 Day(s)", "MAT_INST", "CREDIT_TO_ACC", "DEPTACCT", "0016" }

Now I know that the easiest way to fix this is by changing the delimiter used but unfortunately that is not possible because of legacy datas.

So is there any way to create a regex that ignore the delimiter for the second data?

I have tried the following regex

(?<=FDTYP\/)(.*?)(?=\/FDPERIOD)

and it does get me the second data but it does not work for the rest of the data.

I'm not 100% clear what version of SQL you are using or how you're actually getting that JSON, but assuming the problem spot always occurs between FDTYPE/ and /FDPERIOD, I think something like this should work:

1. Replace only the instances of / in the substring with some character or character combo that you know won't occur in your dataset. In my example I'm using {!} as the replacement.

2. Do you normal routine

3. Go back over extracted data and convert your replacement {!} back to /.

SELECT
	'FDTYP/FD2323*/*/*-/FDPERIOD/31 Day(s)/MAT_INST/CREDIT_TO_ACC/DEPTACCT/0016/' AS ORGDATA,
	CONCAT('FDTYP/', REPLACE(REGEXP_SUBSTR((SELECT ORGDATA), '(?<=FDTYP/)(.*)(?=/FDPERIOD)'), '/', '{!}'), REGEXP_SUBSTR((SELECT ORGDATA), '/FDPERIOD.*(?=/$)')) AS transformed,
	CONCAT('{"', REPLACE((SELECT transformed), '/', '", "'), '"}') AS object_str,
	REPLACE((SELECT object_str), '{!}', '/') AS untransformed

That gets you:

FDTYP/FD2323*/*/*-/FDPERIOD/31 Day(s)/MAT_INST/CREDIT_TO_ACC/DEPTACCT/0016/
FDTYP/FD2323*{!}*{!}*-/FDPERIOD/31 Day(s)/MAT_INST/CREDIT_TO_ACC/DEPTACCT/0016
{"FDTYP", "FD2323*{!}*{!}*-", "FDPERIOD", "31 Day(s)", "MAT_INST", "CREDIT_TO_ACC", "DEPTACCT", "0016"}
{"FDTYP", "FD2323*/*/*-", "FDPERIOD", "31 Day(s)", "MAT_INST", "CREDIT_TO_ACC", "DEPTACCT", "0016"}

https://www.db-fiddle.com/f/4BRg1FG23qhYYFrwFAqqEY/0

In Oracle, you can use the regular expression '^([^/]+)/(.*)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/$' and extract the capturing groups:

SELECT REGEXP_SUBSTR(
         value,
         '^([^/]+)/(.*)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/$',
         1,
         1,
         NULL,
         1
       ) AS col1,
       REGEXP_SUBSTR(
         value,
         '^([^/]+)/(.*)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/$',
         1,
         1,
         NULL,
         2
       ) AS col2,
       REGEXP_SUBSTR(
         value,
         '^([^/]+)/(.*)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/$',
         1,
         1,
         NULL,
         3
       ) AS col3,
       REGEXP_SUBSTR(
         value,
         '^([^/]+)/(.*)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/$',
         1,
         1,
         NULL,
         4
       ) AS col4,
       REGEXP_SUBSTR(
         value,
         '^([^/]+)/(.*)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/$',
         1,
         1,
         NULL,
         5
       ) AS col5,
       REGEXP_SUBSTR(
         value,
         '^([^/]+)/(.*)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/$',
         1,
         1,
         NULL,
         6
       ) AS col6,
       REGEXP_SUBSTR(
         value,
         '^([^/]+)/(.*)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/$',
         1,
         1,
         NULL,
         7
       ) AS col7,
       REGEXP_SUBSTR(
         value,
         '^([^/]+)/(.*)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/$',
         1,
         1,
         NULL,
         8
       ) AS col8
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (value) AS
  SELECT 'FDTYP/FD2323*/*/*-/FDPERIOD/31 Day(s)/MAT_INST/CREDIT_TO_ACC/DEPTACCT/0016/' FROM DUAL UNION ALL
  SELECT 'FDTYP/TESTTYPE/FDPERIOD/31 Day(s)/MAT_INST/CREDIT_TO_ACC/DEPTACCT/0016/' FROM DUAL

Outputs:

Or, to get the output as a single formatted string:

SELECT REGEXP_REPLACE(
         value,
         '^([^/]+)/(.*)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/([^/]+)/$',
         '{"", "", "", "", "", "", "", ""}'
       ) AS data
FROM   table_name;

Which outputs:

fiddle