-
Use Regular Expression to allow specific characters
Hi,
I want to put a validation on a column of a table. The column can have only following characters as a valid character.
0-9, a-z, A-Z, $, *, +, ?, @, °, !, %, &, ', -, /, :, (, ), ., ,, #
if any other characters are used an error is to be returned.
I tried to use regular expression to solve the problem but i am new to it and not able to find a solution.
Please help me out.
Ramchandra Jetwani
-
Check Constraint looks like the appropriate tool for this job.
Code:
alter YOURTABLE
add CONSTRAINT check_YOURCOLUMN
CHECK (YOURCOLUMN IN ('0', '1', '2', ... '#'));
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
The following is a regex version:
Code:
SQL> create table check_reg (
2 a1 varchar2(200)
3 constraint a1_chk check
4 (regexp_replace(a1, '[''0-9a-zA-Z$*+?@°!%&/:().,#-]') is null));
Table created.
SQL> insert into check_reg values ('_');
insert into check_reg values ('_')
*
ERROR at line 1:
ORA-02290: check constraint (TEST.A1_CHK) violated
SQL> insert into check_reg values ('$');
1 row created.
SQL> insert into check_reg values ('ABCD456:)');
1 row created.
SQL> insert into check_reg values ('''');
1 row created.
SQL>
SQL> select * from check_reg;
A1
---------------------------
$
ABCD456:)
'
-
Thank you very much. The suggestion solved my problem.
Ramchandra Jetwani
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|