Usage syntax for Oracle 10g REGEXP_LIKE regular expression function

advertisements

_____________________________________________________________________________________________________________________

Syntax :      REGEXP_LIKE(string, pattern)

                   REGEXP_LIKE(string, pattern, parameters)

Where parameter values can be

i         : is for ignore the case sensitive match

c        : is for case sensitive match

n        : is for make the dot (.) match the new line also

m       : when you specify the m parameter the source string treats as multiple line.       

           Oracle interprets ^ and $ as the start and end, respectively.

You can use REGEXP_LIKE in SQLs, PL/SQLs.

SQL> create table regexp_example as select * from dba_objects where rownum<1000;

Table created.

Fetch Object names with 2 adjacent vowels irrespective of case.

SQL>  select object_name from regexp_example where regexp_like (object_name,'([aeiou])\1','i')

  2  ;

OBJECT_NAME

--------------------------------------------------------------------------------

BOOTSTRAP$MIG

METASTYLESHEET

PL/SQL BOOLEAN

V_$BUFFER_POOL

V$BUFFER_POOL

V_$BUFFER_POOL_STATISTICS

V$BUFFER_POOL_STATISTICS

7 rows selected.

Fetch object names starts with V$L

SQL> select object_name from regexp_example where regexp_like (object_name,'^V\$L');

OBJECT_NAME

--------------------------------------------------------------------------------

V$LOCK_ELEMENT

V$LOCKS_WITH_COLLISIONS

V$LOG

V$LICENSE

V$LOCKED_OBJECT

V$LATCH

V$LATCH_CHILDREN

V$LATCH_PARENT

V$LATCHNAME

V$LATCHHOLDER

V$LATCH_MISSES

V$LOCK

Add check constraints with regexp_like

SQL> create table reexp_example (col1 varchar2(10));

Table created.

SQL> alter table reexp_example add constraint reexp_example_CHK check (regexp_like(col1,'(^[aeiou])'));

Table altered.

SQL>  insert into reexp_example values ('James');

 insert into reexp_example values ('James')

*

ERROR at line 1:

ORA-02290: check constraint (SCOTT.REEXP_EXAMPLE_CHK) violated

SQL> insert into reexp_example values ('Antony');

insert into reexp_example values ('Antony')

*

ERROR at line 1:

ORA-02290: check constraint (SCOTT.REEXP_EXAMPLE_CHK) violated

SQL> insert into reexp_example values ('antony');

1 row created.

SQL> alter table drop constraint reexp_example_CHK;

alter table drop constraint reexp_example_CHK

            *

ERROR at line 1:

ORA-00903: invalid table name

Add check constraint for SSN Number format

SQL> alter table reexp_example drop constraint reexp_example_CHK;

Table altered.

SQL> alter table reexp_example add constraint reexp_example_CHK check (regexp_like(col1,'(^[aeiou])','i'));

Table altered.

SQL> insert into reexp_example values ('Antony');

1 row created.

SQL> insert into reexp_example values ('antony');

1 row created.

SQL> alter table reexp_example add (col2 varchar2(15));

Table altered.

SQL> alter table reexp_example add constraint reexp_example_SSN_CHK check (regexp_like(col2,('^[[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}$')));

Table altered.

SQL> insert into reexp_example values ('Ivan','333-65-2312');

1 row created.

SQL> insert into reexp_example values ('Ivan','1-1-1');

insert into reexp_example values ('Ivan','1-1-1')

*

ERROR at line 1:

ORA-02290: check constraint (SCOTT.REEXP_EXAMPLE_SSN_CHK) violated

_____________________________________________________________________________________________________________________

Website Stats