Regex Solution for Selecting Specific Value from Comma-Separated List with Nulls

When extracting the nth value from a comma-separated list using REGEXP_SUBSTR(), handling null values can be tricky. Here's a detailed examination of the problem and a comprehensive solution.

First, consider the following scenario:

SQL> select REGEXP_SUBSTR('1,2,3,4,5,6', '[^,] ', 1, 2) data
from dual;

D
-
2

This query successfully retrieves the second non-null value ("2") from the list using the regular expression [^,] . However, when the second value is null, the query returns the third item:

SQL> select REGEXP_SUBSTR('1,,3,4,5,6', '[^,] ', 1, 2) data
from dual;

D
-
3

To address this issue, a more flexible regex is needed to allow for optional characters:

SQL> select REGEXP_SUBSTR('1,,3,4,5,6', '[^,]*', 1, 4) data
from dual;

D
-
3

However, this regex also fails for numbers past the null.

Ultimately, the solution lies in a more sophisticated regex:

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

This regex captures the data before the nth occurrence of a comma or the end of the line. The result is:

Data
----

To encapsulate this solution into a reusable function, consider the following code:

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;

This function can be called like this:

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

Regardless of potential nulls or the specific element being selected, this solution provides a robust and elegant method for extracting values from comma-separated lists in Oracle SQL.