How to Reliably Extract Specific Values from Comma-Separated Lists Containing NULLs Using REGEXP_SUBSTR()?

REGULAR EXPRESSION TO SELECT INDIVIDUAL VALUES FROM A LIST WITH NULLS

Issue:

REGEXP_SUBSTR() fails to retrieve the desired nth value from a comma-separated list when nulls are present. When a value is null, it returns the next non-null value as the nth occurrence.

Solution:

To overcome this issue, a modified REGEXP_SUBSTR() expression can be employed:

REGEXP_SUBSTR('1,,3,4,5', '(.*?)(,|$)', 1, 2, NULL, 1)

This expression captures the desired data by following these steps:

1. (.*?): Matches zero or more optional characters.
2. (,|$): Matches either a comma or the end of the line.
3. 1: Specifies that the first matching group (subgroup) is the desired data.
4. 2: Sets the occurrence to select the desired nth value.
5. NULL: Returns null if no match is found for the specified occurrence.

Reusable Function:

For simpler and more convenient usage, this modified REGEXP_SUBSTR() expression can be encapsulated in a PL/SQL function:

FUNCTION GET_LIST_ELEMENT(string_in VARCHAR2, element_in NUMBER, delimiter_in VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
BEGIN
  RETURN REGEXP_SUBSTR(string_in, '(.*?)(\'||delimiter_in||'|$)', 1, element_in, NULL, 1);
END GET_LIST_ELEMENT;

Example Usage:

To retrieve the 4th element from the list '123,222,,432,555':

select get_list_element('123,222,,432,555', 4) from dual;

The above is the detailed content of How to Reliably Extract Specific Values from Comma-Separated Lists Containing NULLs Using REGEXP_SUBSTR()?. For more information, please follow other related articles on the PHP Chinese website!