A. Lemesanyi A. Lemesanyi - 4 months ago 9
SQL Question

Sql Orcle: Regexp_substr

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 for hell 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.

Answer

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)
Comments