Want to create a database column with following checks on oracle database sql

Question

Column should follow below validation needs to be implemented with constraints in oracle db

Should Be Only in Upper Case
Should Not Contain Leading and Trailing Spaces
Should Not Contain Extra Spaces Other Than One Space Between The Words
Should Not Contain Special Characters and Numbers Anywhere in The Text

Give it a try. If you run into problems you can ask a specific question here. — jarlh, Aug 28 '20 at 19:59
https://stackoverflow.com/questions/7621568/oracle-11g-check-constraint-with-regex — Sujitmohanty30, Aug 28 '20 at 20:02
I’m voting to close this question because it contains no effort, seems like dumped homework. — Barbaros Özhan, Aug 29 '20 at 11:53

score 1 · Answer 1 · answered Aug 28 '20 at 20:41

See if this helps.

SQL> create table test
  2    (col  varchar2(20),
  3     --
  4     constraint ch1_upper check (col = upper(col)),
  5     --
  6     constraint ch2_letraspc check (col = trim(col)),
  7     --
  8     constraint ch3_wrdspc check (regexp_like(col, '^ *(\w+ ?)+ *$')),
  9     --
 10     constraint ch4_spec check (regexp_like(col, '^[[:alpha:] ]+$'))
 11    );

Table created.

Testing:

SQL> insert into test values ('abc');
insert into test values ('abc')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CH1_UPPER) violated


SQL> insert into test values (' DEF');
insert into test values (' DEF')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CH2_LETRASPC) violated


SQL> insert into test values ('DEF ');
insert into test values ('DEF ')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CH2_LETRASPC) violated


SQL> insert into test values (' DEF ');
insert into test values (' DEF ')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CH2_LETRASPC) violated


SQL> insert into test values ('GHI23');
insert into test values ('GHI23')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CH4_SPEC) violated


SQL> insert into test values ('GHI#');
insert into test values ('GHI#')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CH4_SPEC) violated


SQL> insert into test values ('GHI   JKL');
insert into test values ('GHI   JKL')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CH3_WRDSPC) violated


SQL> insert into test values ('GHI JKL    MNO');
insert into test values ('GHI JKL    MNO')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CH3_WRDSPC) violated


SQL> insert into test values ('GHI JKL MNO');

1 row created.

SQL>

I want do this without regex. – Ahmed Ahmed Aug 29 '20 at 03:45 — Ahmed Ahmed, Aug 29 '20 at 03:45

Want to create a database column with following checks on oracle database sql

1 Answers1