SQL & PL/SQL

Hello.

I am new to regular expressions.

I am writing a pl/sql program that reads a text file/xls and uploads data to a staging table in Oracle 10g.

Requirement is to prevent anything other than alphanumeric data being entered in any varchar2 columns with the exception of dash,underscore,apostrophe,period also permitted. Any other punctuation or special character should cause an error on insert/update.

I am trying to implement a check constraint with a regular expression on varchar2 columns like:

CREATE TABLE TEST_CHARS (TEXT1 VARCHAR2(100));

ALTER TABLE test_chars
ADD CONSTRAINT chk_spec_char1
CHECK (REGEXP_LIKE (text1, '^[ [:alnum:]+[\-]+$')
);

The above seems to work fine as it only checks for alphanumeric and dashes. When I extend it out to include underscore and period it stops working and allows entry of other undesired values. Handling the apostrophe is also problematic.

ALTER TABLE test_chars
ADD CONSTRAINT chk_spec_char1
CHECK (REGEXP_LIKE (text1, '^[ [:alnum:]+[\-]+[\_]+[\.]+$')
);

I have also tried the following with similar results.

ALTER TABLE test_chars
ADD CONSTRAINT chk_spec_char1
CHECK (REGEXP_LIKE (text1, '^[[:alnum:]-_.]*$')
);

I believe the check constraint with the regexp_like is the way to go but I am unable to come up with the correct
expression.

Any pointers are greatly appreciated.


Andy