REGEXP Substring Oracle

Question

I'm looking for a regexp to get the correct file name from the below string.

Input

Report_18072014.pdf

Expected Output

Report.pdf

The query so far which gives me the correct output.

REPLACE(FILENAME, '_' || TRIM(REGEXP_SUBSTR (FILENAME, '[^_.]+',1,2)))

My question is if there is a better regexp I can use to avoid concatenating _ to the substring in the replace function.

zx81 · Accepted Answer · 2014-07-22 01:48:01Z

2

With your file format, you can use something like this:

REGEXP_REPLACE(FILENAME, '^([[:alnum:]]+)_[^.]+(\.[^.]+)',
                         '\1\2',
                         1, 0, 'c')

Explanation

The ^ anchor asserts that we are at the beginning of the string
([[:alnum:]]+) captures letters to Group 1.
[^.]+ matches any chars that are not a .
(\.[^.]+) captures the extension to Group 2 (the dot and any chars that are not a dot)
\1\2 replaces with Group 1, Group 2

edited Jul 22, 2014 at 1:48

answered Jul 22, 2014 at 1:33

zx81

42k10 gold badges92 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

zx81 Over a year ago

FYI: added explanation for the regex. :)

Gaurav S Over a year ago

Thanks @zx81. This one worked as expected for me. REGEXP_REPLACE(FILENAME,'_[[:alnum:]]+') +1 for the explanation though.

Gaurav S Over a year ago

Thanks. Accepted the answer.

zx81 Over a year ago

Thanks. FYI if you know it's only digits after the underscore, you can use [[:digit:]]+ instead of [[:alnum:]]+ :)

Doug · Accepted Answer · 2014-07-22 02:00:29Z

1

Try using this [untested]:

REGEXP_REPLACE(FILENAME, '_[[:digit:]]*\.', '.')

I really enjoy using regular expressions, but I wish that Oracle used the PCRE format instead of the POSIX format.

Explanation:

_[[:digit:]]* matches the underscore and 0 or more digits.
. matches the period to make sure that you are beside the file extension
The final , '.' replaces everything with a period so that you have still have the .PDF

I would have changed the replacement to '.PDF', but I don't want to force your file names to mixed case files.

edited Jul 22, 2014 at 2:00

answered Jul 22, 2014 at 1:55

Doug

3,9723 gold badges25 silver badges18 bronze badges

1 Comment

Gaurav S Over a year ago

Thanks. This worked as well. But accepting @zx81 solution for it being more generic of replacing string between 2 characters.

Collectives™ on Stack Overflow

REGEXP Substring Oracle

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related