Usage syntax for Oracle 10g REGEXP_LIKE regular expression function

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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google