Use Regular Expression to allow specific characters

[ram_jetwani]

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.

[PAVB]

Check Constraint looks like the appropriate tool for this job.

alter YOURTABLE
add CONSTRAINT check_YOURCOLUMN
   CHECK (YOURCOLUMN IN ('0', '1', '2', ... '#'));

[ebrian]

The following is a regex version:

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:)
'

[ram_jetwani]

Thank you very much. The suggestion solved my problem.