Oracle SQL getting the nth element regexp

Question

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?

Elisavetpol

When you see CSV or otherwise serialized values in an RDBMS you know something's not right. – Sternforemost

@NullUserException, good point. Unfortunately I've come across it too many times :-( – Ahlers

Answer 1

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

Ahlers

Hello Ollie, I have same problem but your advise doesn't work on my case. I need to take 3rd value from the string select regexp_substr('SENDER ,3B13 ,3 ,300 , , , , , ,', '[[:alnum:]]{0,},', 1, 2) from dual; but I am getting only , string. Can you please advise what I am doing wrong? – Camey

Answer 2

Unless you're stuck on regular expressions, this works as well:

WITH q AS (
SELECT '100016154,5101884LT00001,,,,,100000010892100000012655,L,SEI,5101884LT00001,1,SL,3595.03,00,2,N,N,G,N' thestring FROM dual
)
SELECT SUBSTR(thestring, INSTR(thestring,',',1,6)+1, 
                         INSTR(thestring,',',1,7)-INSTR(thestring,',',1,6)-1) "The Element"
  FROM q;

The Element
------------------------
100000010892100000012655

Another possibility. You have not specified what the source of your data is. Could you possibly use an external table to read your input source and process it via SQL?

Brey

would there be an overhead in calling INSTR three times in the statement as opposed to calling REGEXP_SUBSTR only once? (I realise the length of the delimited string hasn't been specified and would be a factor). – Ahlers

@Ollie, it might, only by measuring the results would you know for sure. REGEXP_* functions are not without their own overhead issues. – Brey

Answer 3

You can do it with a little trick: first replacing all commas by a comma followed by a space, and afterwards skip that extra leading space:

SQL> with data as
  2  ( select '100016154,5101884LT00001,,,,,100000010892100000012655,L,SEI,5101884LT00001,1,SL,3595.03,00,2,N,N,G,N' txt
  3      from dual
  4  )
  5  select regexp_substr(txt,'[^,]+',1,7)                             seventh_element_wrong
  6       , replace(txt,',',', ')                                      with_extra_space_after_comma
  7       , regexp_substr(replace(txt,',',', '),'[^,]+',1,7)           seventh_element_leading_space
  8       , substr(regexp_substr(replace(txt,',',', '),'[^,]+',1,7),2) the_seventh_element
  9    from data
 10  /

S WITH_EXTRA_SPACE_AFTER_COMMA
- ----------------------------------------------------------------------------------------------------------------------
SEVENTH_ELEMENT_LEADING_S THE_SEVENTH_ELEMENT
------------------------- ------------------------
1 100016154, 5101884LT00001, , , , , 100000010892100000012655, L, SEI, 5101884LT00001, 1, SL, 3595.03, 00, 2, N, N, G, N
 100000010892100000012655 100000010892100000012655

Regards,
Rob.

Congregation

Answer 4

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

Coumarone