REGEX to select nth value from a list, allowing for nulls

Question

I am using REGEXP_SUBSTR() to return the nth value from a comma-separated list. This works fine when all values are present, but fails if an item is null. Here is an example that works where all values are present and I am selecting the 2nd occurrence of 1 or more characters that are not a comma:

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

D
-
2

But when the second value is null, I am really getting the third item in the list, which of course really is the 2nd occurrence of 1 or more characters that are not a comma. However, I need it to return NULL as the 2nd item is empty:

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

D
-
3

If I change the regex to allow for zero or more characters instead of 1 or more, it also fails for numbers past the null:

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

D
-
3

I need to allow for the null but can't seem to get the syntax right. Logically I need to return what is before the nth occurrence of a comma whether data is present or not (and allow for the last value also). Any ideas?

This is a duplicate. Parsing a csv string with nulls is a common question (e.g. http://stackoverflow.com/questions/25529511/oracle-regex-substr-not-honoring-null-values/25530536#25530536). I am partial to my solution (precede the string with a comma and have your regex pattern be of the form ',[^,]*'. Next, LTRIM the matching regexp_substr. — Patrick Bacon, Sep 03 '14 at 16:43

Gary_W · Accepted Answer · 2015-09-04T14:24:55.197

Thanks to those who replied. After perusing your answers and the answers in the link supplied, I arrived at this solution:

SQL> select REGEXP_SUBSTR('1,,3,4,5', '(.*?)(,|$)', 1, 2, NULL, 1) data
  2  from dual;

Data
----

Which can be described as "look at the 2nd occurrence of an optional set of zero or more characters that are followed by a comma or the end of the line, and return the 1st subgroup (which is the data less the comma or end of the line).

I forgot to mention I tested with the null in various positions, multiple nulls, selecting various positions, etc.

The only caveat I could find is if the field you look for is greater than the number available, it just returns NULL so you need to be aware of that. Not a problem for my case.

EDIT: I am updating the accepted answer for the benefit of future searchers that may stumble upon this.

The next step is to encapsulate the code so it can be made into a simpler, reusable function. Here is the function source:

  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 hides the regex complexities from developers who may not be so comfortable with it and makes the code cleaner anyway when in use. Call it like this to get the 4th element:

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

I do like the use of the alternation operator and the use of character groups and the ability to pass a subexpression (e.g. 1 as the first subexpression). I did not realize this added feature in 11g. — Patrick Bacon, Sep 03 '14 at 20:27
@Aramillo - thanks! interesting learning experience today. Patrick - I didn't either until I read through that post you pointed me to and re-googled the man page for REGEXP_SUBSTR while specifying 11g. So, thanks! — Gary_W, Sep 03 '14 at 20:34

score 0 · Answer 2 · answered Sep 03 '14 at 15:55

How about something brutal like this:

select REGEXP_SUBSTR(replace('1,,3,4,5,6', ',,', ',NULL,'), '[^,]+', 1, 2) data
from dual

That returns the string value. You can get a real NULL using a case:

select (case when REGEXP_SUBSTR(replace('1,,3,4,5,6', ',,', ',NULL,'), '[^,]+', 1, 2) = 'NULL'
             then NULL
             else REGEXP_SUBSTR(replace('1,,3,4,5,6', ',,', ',NULL,'), '[^,]+', 1, 2)
        end)
from dual;

There may be a regexp_-only solution, but this is what first comes to mind.

Brutal indeed :) Might work fine, until you try to load data that contains NULL as text, and you want to keep it that way. Not a bad idea though... — Wouter, Aug 14 '15 at 12:41

REGEX to select nth value from a list, allowing for nulls

2 Answers2

Linked