René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

regexp_replace

regexp_replace (string, pattern)
regexp_replace (string, pattern, replace-string)
regexp_replace (string, pattern, replace-string, position)
regexp_replace (string, pattern, replace-string, position, occurence)
regexp_replace (string, pattern, replace-string, position, occurence, parameters)
parameters can be a combination of
  • i: to match case insensitively
  • c: to match case sensitively
  • n: to make the dot (.) match new lines as well
  • m: to make ^ and $ match beginning and end of a line in a multiline string
regexp_substr is an Oracle SQL function that enables regular expressions in queries. It enhances the 'traditional' substr.
regexp_replace is an Oracle SQL function that enables regular expressions in queries. It enhances the 'traditional' replace.

Demonstration

create table strings (
  str varchar2(30)
);

create table patterns (
  pat  varchar2(60),
  repl varchar2(30),
  dsc  varchar2(30)
);

insert into patterns values ('^[[:space:]]*[^[:space:]]+[[:space:]]+([^[:space:]]+).*', '\1', 'The 2nd word');
insert into patterns values ('^[^[:digit:]]*([[:digit:]]*\.?[[:digit:]]+).*'          , '\1', 'The 1st number');
insert into patterns values ('^[^[:upper:]]*([[:upper:]]+).*'                         , '\1', 'Uppercase word');

insert into strings values ('foo  bar   baz');
insert into strings values ('bla   MOO   82.22    7.34  bla');
insert into strings values ('  one   two 3 four  ');

column found format a20

select
  str,
  regexp_replace(str, pat, repl) found,
  dsc
from
  strings cross join patterns
where
  regexp_instr(str,pat) > 0;

STR                            FOUND                DSC
------------------------------ -------------------- --------------------
foo  bar   baz                 bar                  The 2nd word
bla   MOO   82.22    7.34  bla MOO                  The 2nd word
  one   two 3 four             two                  The 2nd word
bla   MOO   82.22    7.34  bla 82.22                The 1st number
  one   two 3 four             3                    The 1st number
bla   MOO   82.22    7.34  bla MOO                  Uppercase word

Links

See also On using regexp_replace to format data.