CODEWITHSUNDEEP
sqlregexoracle-databaseoracle11g
browning0
browning0

Reputation: 911

Regular expressions in check constraint: does not accept numbers

How to add a constraint on column first_name (varchar2(20)) in customers table so that the value inserted in the column does not contain numbers?

I think I've found possible solution:

ALTER TABLE customers

ADD CONSTRAINT first_name_check

CHECK (REGEXP_LIKE(first_name,'^[[:alpha:][:blank:][:cntrl:][:punct:]]+$')) NOVALIDATE;

Also:

ALTER TABLE customers

ADD CONSTRAINT first_name_check

CHECK (NOT REGEXP_LIKE(first_name,'[[:digit:]]')) NOVALIDATE;

However, is there better way to do it? Without using all those character classes and without "NOT" before REGEXP_LIKE?

I thought this would work:

ALTER TABLE customers

ADD CONSTRAINT first_name_check

CHECK (NOT REGEXP_LIKE(first_name,'^[^[:digit:]]+$')) NOVALIDATE;

But I was wrong. Could you explain me why?

Upvotes: 0

Views: 1449

Answers (2)

Ed Gibbs
Ed Gibbs

Reputation: 26343

As for the question "is there a better way to do it" regarding this constraint:

CHECK (NOT REGEXP_LIKE(first_name,'[[:digit:]]'))

It's a subjective question, so here's a subjective answer: I don't think so. It does the job, it's character-set safe, and it's easy to read (for a regex).

Upvotes: 3

rutter
rutter

Reputation: 11452

Your last regexp doesn't seem quite right. ^[^[:digit:]]+$ should match any string that's not made up of digits, but then you invert the check.

Either get rid of the NOT, or try one more like this: [$.*[:digit:].*^] to match any sequence of characters, one digit, then any sequence of characters.

Example on SQL Fiddle.

Upvotes: 1

Related Questions