Sql Oracle: Regexp_substr

Question

I have the following expression: 15-JUL-16,20-JUL-16,20-JUL-16,30-JUL-16 in one of my columns.

I successfully used SUBSTR(REGEXP_SUBSTR(base.systemdate, '.+,'), 1, 9) to get 15-JUL-16 (expression until first comma) from the expression.

But I can't figure out how to get 30-JUL-16 (the last expression after last comma).

Is there some way to use REGEXP_SUBSTR to get that? And since we are at it.

Is there a neat way to only use REGEXP_SUBSTR to get 15-JUL-16 without comma? Because I am using second SUBSTR to get rid of the comma, so I can get it compatible with data format.

Don't store data as comma separated items, have one value per column/row. — jarlh, Aug 02 '16 at 11:00
You are looking to split a comma-separated string. This question has been asked and answered so many times - just google for "Oracle split csv" or such. — , Aug 02 '16 at 11:11
Thanks for suggestion, but I can't control the way data is stored in our Data warehouse. I just work with what I get. But to repeat my question. How can I rewrite SUBSTR(REGEXP_SUBSTR(base.systemdate, '.+,'), 1, 9) to only use REGEXP_SUBSTR without the additional SUBSTR to get the first date before the first comma without the comma? — A. Lemesanyi, Aug 02 '16 at 11:20
@A.Lemesanyi - so, did you try what I suggested? I remember answering exactly this question at least 3-4 times just here on SO and just in the last few weeks. — , Aug 02 '16 at 12:27
Yep, I already found it. Thanks again for your suggestions :-) — A. Lemesanyi, Aug 02 '16 at 13:18

Gordon Linoff · Accepted Answer · 2016-08-03T01:28:20.667

0

You can use a very similar construct:

SELECT REGEXP_SUBSTR(base.systemdate, '[^,]+$')

Oracle (and regular expressions in general) are "greedy". This means that they take the longest string. If you know the items in the list are all the same length, you could just use:

SELECT SUBSTR( ase.systemdate, -9)

edited Aug 03 '16 at 01:28

answered Aug 02 '16 at 11:01

Gordon Linoff

1,242,037
58
646
786

Thanks a lot :) The thing is the length of expression varies. Sometimes there are 2 dates, sometimes 4, sometimes 6. And I am always interested in first one and last one (without comma), so I can work with it as dates in future. – A. Lemesanyi Aug 02 '16 at 11:07
Did you mean `[^,]+` ? - the + is missing. – Aug 02 '16 at 11:10
yep. The + thing was missing. :) But otherwise it is working. And how can I rewrite SUBSTR(REGEXP_SUBSTR(base.systemdate, '.+,'), 1, 9) to only use REGEXP_SUBSTR without the additional SUBSTR to get the first date before the first comma without the comma? – A. Lemesanyi Aug 02 '16 at 11:12

score 0 · Answer 2 · answered Aug 02 '16 at 11:15

Try this

    select dates from
    (
        SELECT dates,max(id) over (partition by null) lastrec,min(id) over (partition by null) firstrec,id FROM (
            with mine as(select '15-JUL-16,20-JUL-16,20-JUL-16,30-JUL-16' hello from dual)
               select rownum id,regexp_substr(hello, '[^,]+', 1, level) dates from mine
               connect by regexp_substr(hello, '[^,]+', 1, level) is not null)
     )
     where id=firstrec or id=lastrec

this query give you first and last record from comma separated list.

Sql Oracle: Regexp_substr

2 Answers2