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?

When you see CSV or otherwise serialized values in an RDBMS you know something's not right. — NullUserException, Sep 09 '11 at 14:41
@NullUserException, good point. Unfortunately I've come across it too many times :-( — Ollie, Sep 09 '11 at 14:44

Ollie · Accepted Answer · 2011-09-09T14:38:30.687

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

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? — Reddy SK, Sep 13 '16 at 07:56

DCookie · Answer 2 · 2011-09-09T15:30:42.193

1

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?

edited Sep 09 '11 at 15:30

answered Sep 09 '11 at 15:12

DCookie

42,630
11
83
92

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). – Ollie Sep 09 '11 at 15:14
@Ollie, it might, only by measuring the results would you know for sure. REGEXP_* functions are not without their own overhead issues. – DCookie Sep 09 '11 at 15:25

score 0 · Answer 3 · edited Oct 22 '13 at 14:52

0

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;

edited Oct 22 '13 at 14:52

kleopatra

51,061
28
99
211

answered Oct 22 '13 at 14:40

stan

1

score 0 · Answer 4 · answered Sep 09 '11 at 14:42

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.

Oracle SQL getting the nth element regexp

4 Answers4

Linked