SQL & PL/SQL

Hi,

I have a column which stores a comma delimited list of values. Some of these values in the list may be null. I'm having some issues trying to extract the values using the REGEXP_SUBSTR function when null values are present. Here are two things that I've tried:

SELECT
   REGEXP_SUBSTR (val, '[^,]*', 1, 1) pos1
  ,REGEXP_SUBSTR (val, '[^,]*', 1, 2) pos2
  ,REGEXP_SUBSTR (val, '[^,]*', 1, 3) pos3
  ,REGEXP_SUBSTR (val, '[^,]*', 1, 4) pos4
  ,REGEXP_SUBSTR (val, '[^,]*', 1, 5) pos5
FROM (SELECT 'AAA,BBB,,DDD,,FFF' val FROM dual);

POS P POS P P
--- - --- - -
AAA   BBB

SELECT
   REGEXP_SUBSTR (val, '[^,]+', 1, 1) pos1
  ,REGEXP_SUBSTR (val, '[^,]+', 1, 2) pos2
  ,REGEXP_SUBSTR (val, '[^,]+', 1, 3) pos3
  ,REGEXP_SUBSTR (val, '[^,]+', 1, 4) pos4
  ,REGEXP_SUBSTR (val, '[^,]+', 1, 5) pos5
FROM (SELECT 'AAA,BBB,,DDD,,FFF' val FROM dual);

POS POS POS POS P
--- --- --- --- -
AAA BBB DDD FFF

As you can see neither of the calls works correctly. Does anyone know how to modify the regular expression pattern to handle null values? I've tried various other patterns but was unable to get anyone to work for all cases.

Thanks,

Martin
-----
http://www.ClariFit.com
http://www.TalkApex.com