sqlregexoracle-databasedenormalized

Oracle SQL getting the nth element regexp

I am trying to get the nth element in a comma separated string using SQL in Oracle.

I have the following so far..

SELECT regexp_substr(
   '100016154,5101884LT00001,,,,,100000010892100000012655,L,SEI,5101884LT00001,1,SL,3595.03,00,2,N,N,G,N',
   '[^,]+',
   1,
   7)
FROM dual;

but it doesn't work when the element is empty i.e. ,, can anyone help?

Solution

  • If your delimited values are always alphanumeric in between the commas then you could try:

    SELECT REGEXP_SUBSTR( <delimied_string>, '[[:alnum:]]{0,},', 1, 7 )
  FROM dual;

    To get the seventh value (including the trailing comma). If it is empty you just get the trailing comma (which you can easily remove).

    Obviously, if you want a value other than the seventh then change the fourth parameter value to whichever nth occurrance you want e.g.

    SELECT REGEXP_SUBSTR( <delimied_string>, '[[:alnum:]]{0,},', 1, <nth occurance> )
  FROM dual;

    EDIT: As I love REGEX here is a solution that also removes the trailing comma

    SELECT REPLACE(
          REGEXP_SUBSTR(<delimied_string>, '[[:alnum:]]{0,},', 1, <nth>), 
          ','
       )
  FROM dual;

    hope it helps