How to Extract the nth Value from a Comma-Separated String, Handling Nulls?

Extracting nth Value from a Comma-Separated List with Null Handling

When using REGEXP_SUBSTR() to extract the nth value from a comma-separated list, handling null values can be challenging. To achieve this, a modification to the regular expression is necessary.

The original expression, REGEXP_SUBSTR('1,,3,4,5', '[^,] ', 1, 2), returns the second occurrence of one or more non-comma characters. However, when encountering a null, it interprets it as the start of a new non-empty value and returns the third item instead of the expected null.

To address this, the expression is modified to allow for zero or more non-comma characters: REGEXP_SUBSTR('1,,3,4,5', '(.*?)(,|$)', 1, 2, NULL, 1). It identifies the desired value by looking for either zero or more characters followed by a comma or the end of the line, and returns only the first subgroup (the desired data).

This enhanced expression handles nulls correctly, returning null if the second item is empty. The function GET_LIST_ELEMENT() encapsulates this logic for easier reuse:

  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 approach accommodates nulls and allows for the selection of any value from the list.

The above is the detailed content of How to Extract the nth Value from a Comma-Separated String, Handling Nulls?. For more information, please follow other related articles on the PHP Chinese website!