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?
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