Oracle Query for extracting comma separated values

user_13686778 0
Viewed 688

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.

csv oracle sql regex
2 Answers

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, ',');
user_187470

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)
user_106767160
Related