Skip to Main Content

Enhancements to REGEXP function

michaelrozar17Mar 9 2015 — edited Jan 11 2016

Recently I came across this thread, where in, a VARCHAR2 column that contains strings/series of words find the word that is in upper case and convert that alone to initcap format. To achieve this the know method to me is to 1) break the sentence into separate row each containing single word 2) find the word that is in upper case 3) convert it to initcap format 4) rejoin the words to form the sentence back again.

Instead to follow these many steps, if I had a regexp_replace in which I can use INTICAP function on the  replace_string expression it would be as simple as the following:

with t as (select 'A reading from the Letter of Saint Paul to the Romans: BROTHERS and sisters: Whatever was written previously' cols from dual union all

           select 'The uppercase WORDS are often at the BEGINNING of each field' from dual

           )

select regexp_replace(cols,'([A-Z][A-Z]+)',INTICAP('\1')  )  cols_final

   from t

;

In general db should allow us to apply Oracle defined functions on the replace_string expression part of any/applicable regexp_* functions. This idea is similar to the one that exists in unix/perl: " 's/\(.*\)/\L\1/' "

Comments
Post Details
Added on Mar 9 2015
14 comments
571 views