Search code examples
sqloracle-databaseregexp-replace

SQL Oracle - Replace character in string between two vowels

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?

Solution

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