Search code examples
sqloracle-databasesubstrregexp-substr

Sql Oracle: 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 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.

Solution

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