add a check constraint for the column

Question

Add a check constraint for the column, ONLY insert the values that didn't have any special characters except comma, space, full-stop and number in them?

For example allows like: Group Co.,Ltd

Should not allow ¿Abc is important

You can use regular expressions in check constraint. https://stackoverflow.com/questions/7621568/oracle-11g-check-constraint-with-regex — Nitish, Jul 28 '17 at 11:06
Possible duplicate of [Oracle 11g - Check constraint with RegEx](https://stackoverflow.com/questions/7621568/oracle-11g-check-constraint-with-regex) — Nitish, Jul 28 '17 at 11:08

Himanshujaggi · Answer 1 · 2017-07-28T12:09:17.097

1

You can try the below example-

ALTER TABLE EMP ADD ( CONSTRAINT CC_NAME CHECK (TRIM(TRANSLATE(UPPER(ename),' ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_,.',' ')) is null));

edited Jul 28 '17 at 12:09

answered Jul 28 '17 at 11:05

Himanshujaggi

391
1
9

Thank you but it working only string and blank space " Ram kanaka 123 bb.," not allows (i.e comma, full-stop and number not considering )please help me. – P Nayak Jul 28 '17 at 11:28
V. V. Hangzhou ram. also not considering .. as per my requirement it should be considerable..Please help..Thanks in Advance – P Nayak Jul 28 '17 at 11:33
It works. Make sure you don't have any other check constraint on same and also verify if there is any validation code written in trigger. – Himanshujaggi Jul 28 '17 at 11:35
I have updated the code and added trim now check if this works for you. – Himanshujaggi Jul 28 '17 at 12:09
Your welcome! please mark the answer helpful and correct. – Himanshujaggi Jul 29 '17 at 04:57

add a check constraint for the column

1 Answers1