Thinking Out Loud

November 30, 2012

REGEXP & Needle in Haystack

Filed under: 11g,oracle — mdinh @ 5:17 am

There’s a process that looks for and corrects bad data. An analogy of the process is like looking for a needle in a haystack.

As the haystack gets larger and larger, it becomes more difficult to find the needle. Why not prevent the needle from falling into the haystack to being with?

Currently, the function being used is –┬áREGEXP_INSTR(name,'[[:lower:]]’,1,1) <> 0

The function looks for the first occurrence of a lower case alphabet and returns the position in the string.

Example:

SQL> select  REGEXP_INSTR('MiCHaEl','[[:lower:]]',1,1) pos from dual;
       POS
----------
         2

SQL> select  REGEXP_INSTR('MiCHaEl','[[:lower:]]',1,2) pos from dual;
       POS
----------
         5

SQL> select  REGEXP_INSTR('MiCHaEl','[[:lower:]]',1,3) pos from dual;
       POS
----------
         7

Why not create a constraint to prevent the bad data in the first place and to avoid correction?

Example:

SQL> select * from t where REGEXP_INSTR(name,'[[:lower:]]',1,1) <> 0;

NAME
------------------------------
MICHAEl
MICHaEL

SQL> delete from t where name in ('MICHaEL','MICHAEl');

2 rows deleted.

SQL> commit;

Commit complete.

SQL> alter table t add CONSTRAINT ckt CHECK(REGEXP_INSTR(name,'[[:lower:]]',1,1)=0);

Table altered.

SQL> insert into t values('MICHaEL');
insert into t values('MICHaEL')
*
ERROR at line 1:
ORA-02290: check constraint (OPS$ORACLE.CKT) violated

SQL> insert into t values('MICHAEl');
insert into t values('MICHAEl')
*
ERROR at line 1:
ORA-02290: check constraint (OPS$ORACLE.CKT) violated

Alternatively, REGEXP_LIKE can also be used.

SQL> alter table t drop constraint ckt;
Table altered.

SQL> alter table t add CONSTRAINT ckt CHECK(NOT REGEXP_LIKE(name,'[[:lower:]]'));
Table altered.

SQL> insert into t values('MICHaEL');
 insert into t values('MICHaEL')
 *
 ERROR at line 1:
 ORA-02290: check constraint (OPS$ORACLE.CKT) violated

SQL> insert into t values('MICHAEl');
 insert into t values('MICHAEl')
 *
 ERROR at line 1:
 ORA-02290: check constraint (OPS$ORACLE.CKT) violated
About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 313 other followers

%d bloggers like this: