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
