troubleshooting Question

oracle sql splitting a comma-separated string but ignoring commas in quotes

Avatar of suvmitra
suvmitraFlag for India asked on
Oracle Database* Oracle PL/SQLSQL
21 Comments1 Solution258 ViewsLast Modified:

Kindly help on the following problem. I am using Oracle SQL to split comma separated string values. However, need to ignore the values with comma.

Below is the sample data, which needs to be separated/split (comma seperated)
'franchise': 'Px Oncology (Solid Tumors, all XA Onc projects)', 'zlinical_xombination_study': 'No'

sample output would be,
franchise: Px Oncology (Solid Tumors, all XA Onc projects)
zlinical_xombination_study: No

problem is, the value 'franchise': 'Px Oncology (Solid Tumors, all XA Onc projects)' is also having a comma within its value, need to ignore this comma while splitting

I am using the below code,
select LTRIM (REGEXP_SUBSTR (ERROR_TEXT, '[^,]+', 1, 1), ',')    AS part_1 FROM ERROR_LOG_TEMP_5;

The output I am getting is ['franchise': 'Px Oncology (Solid Tumors]

It should provide,  ['franchise': 'Px Oncology (Solid Tumors, all XA Onc projects)']
Join our community to see this answer!
Unlock 1 Answer and 21 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 21 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros