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

regexp_like

regexp_like (string, pattern);
regexp_like (string, pattern, 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_like is an Oracle SQL function that enables regular expressions in queries. It enhances the «traditional» like.
regexp_like is a pattern condition.

Demonstration

create table strings (
  str  varchar2(30)
);

create table patterns (
  pat  varchar2(50),
  dsc  varchar2(30)
);

insert into patterns values ('^[[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}$', 'Social security number');
insert into patterns values ('[^[:alpha:]]([[:alpha:]]+)[^[:alpha:]] *\1'    , 'Repeated words');
insert into patterns values ('^([[:lower:]]| )*$'                            , 'Only lowercase words');
insert into patterns values ('^[[:digit:]]+$'                                , 'Only digits');
insert into patterns values ('[[:digit:]]'                                   , 'At least one digit');
insert into patterns values ('^-?[[:digit:]]*\.?[[:digit:]]+$'               , 'Number');


insert into strings values ('987-65-4321');
insert into strings values ('hello foo bar');
insert into strings values ('4987-65-4321');
insert into strings values ('hello FOO BAR');
insert into strings values ('-4.55');
insert into strings values ('987-65-43213');
insert into strings values ('4.55');
insert into strings values ('hello bar bar');
insert into strings values (' 4.55');
insert into strings values ('1234567890');
insert into strings values ('hello FOO FOO');

select
  str,dsc 
from 
  strings cross join patterns
where
  regexp_like(str, pat) ;

STR                            DSC
------------------------------ ------------------------------
987-65-4321                    Social security number
hello bar bar                  Repeated words
hello FOO FOO                  Repeated words
hello foo bar                  Only lowercase words
hello bar bar                  Only lowercase words
1234567890                     Only digits
987-65-4321                    At least one digit
4987-65-4321                   At least one digit
-4.55                          At least one digit
987-65-43213                   At least one digit
4.55                           At least one digit
 4.55                          At least one digit
1234567890                     At least one digit
-4.55                          Number
4.55                           Number
1234567890                     Number