SQL Oracle - Replace character in string between two vowels

Question

I already read all REGEXP_REPLACE documentation, but didn't found anything that I looking for. I want to replace a specificate charater between two vowels to another charater.

Example:

String: abcdeZebca Output: abcdeSebca

The letter Z was replaced by S, cause its was between two vowels. Thats possible in SQL Oracle?

Answer 1

I'm guessing you didn't catch the bit about backreferences in the docs though:

SELECT
  REGEXP_REPLACE(yourcolumn, '([aeiou])Z([aeiou])', '\1S\2')
FROM 
  yourtable

Explained:

[aeiou] means match any single vowel. Surrounding it in brackets means "and remember what you found into a numbered slot, starting with 1" slots are numbered from left to right throughout the entire expression - each (brackets expression) gets its own number

Hence the full expression means: - find any vowel and store in slot 1 - followed by Z - followed by any vowel and store in slot 2

The replacement string is: - the contents of slot 1 - S - the contents of slot 2

Hence

aZe -> aSe
eZi -> eSi

And so on..