Search code examples
regexoracle-databaseregexp-replace

Regular Expression Match exact length of digits

I want to replace an exact quantity of consecutive digits in a string, meaning I don't want it to replace occurrences that are part of a longer sequence of digits.

For example:

select regexp_replace('filename07_20160723', '[0-9]{2}', '') from dual

returns

filename_

but I want it to return

filename_20160723

How do I replace only the solitary '07' (that has non-digit characters to either side)?

Solution

  • Because oracle doesn't support look arounds, to replace exactly 2 digits (without digits either side), capture non-digits either side and put them back:

    regexp_replace('filename07_20160723', '(\D)\d\d(\D)', '\1\2')

    Note: As @mathguy points out, the non-digit characters either side are consumed, so they can't participate in another match. This has the following effect:

    x33y44z -> xy44z not xyz

    because 'y' can't participate again to allow a match on the second digit pair.