Oracle Query for extracting comma separated values

Question

I have a requirement in which I am getting a string with comma separated values and I have to extract the values and process. I used the query below and everything was working fine but it failed for the scenario when i have all null values in that string separated by ,.

SELECT regexp_substr(i_child_sal_acc_det, '[^,]+', 1, LEVEL)
  BULK COLLECT INTO v_sal_acc_det_list
  FROM dual
CONNECT BY regexp_substr(i_child_sal_acc_det, '[^,]+', 1, LEVEL)
IS NOT NULL.

The above query returns 4 records for input 'A,B,C,D' but only 1 record for ',,,' and 2 records for input ',C,D,'.

What i want is to fetch the values between commas even if it is NULL, and they can pass at max 9 values in a string.

Can you please help.

GMB · Accepted Answer · 2020-06-10 08:43:08Z

2

The problem is in the exit condition of the recursive query, that stops as soon as an empty item is met. You could change that to check the iteration index against the actual number of commas in the string:

SELECT regexp_substr(i_child_sal_acc_det, '[^,]*', 1, LEVEL)
BULK COLLECT INTO v_sal_acc_det_list
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(i_child_sal_acc_det, ',') + 1

Note that I changed the quantifier of regular expression from + to * so it allows empty strings.

If you want to limit the number of iterations to 9, even if there are more items in the CSV string:

CONNECT BY LEVEL <= LEAST(REGEXP_COUNT(i_child_sal_acc_det, ',') + 1, 9)

edited Jun 10, 2020 at 8:43

answered Jun 10, 2020 at 8:38

GMB

224k25 gold badges102 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tony Andrews · Accepted Answer · 2020-06-10 08:37:53Z

1

If you can access the Oracle apex_string package you can do this:

SELECT column_value
  BULK COLLECT INTO v_sal_acc_det_list
  FROM apex_string.split (i_child_sal_acc_det, ',');

answered Jun 10, 2020 at 8:37

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

1 Comment

Alok Mathur Over a year ago

I don't have access to this package, but thank you Tony.

Collectives™ on Stack Overflow

Oracle Query for extracting comma separated values

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related