Oracle: Check constraint for both alphabetical and numerical characters in varchar2

Question

So I want to check that the varchar2 is in the format of 4 alphabetical characters then 3 numerical characters e.g. AABB123 or LMNO987

So far I've tried:

CONSTRAINT Code_Check check (REGEXP_LIKE(Code,'[^A-Z][^A-Z][^A-Z][^A-Z][0-9][0-9][0-9]')) CONSTRAINT Check_Code check (Code LIKE '[^A-Z][^A-Z][^A-Z][^A-Z][0-9][0-9][0-9]' CONSTRAINT Check_Code check (Code LIKE '[A-Z][A-Z][A-Z][A-Z][0-9][0-9][0-9]') CONSTRAINT Check_Code check (Code regexp_LIKE '[A-Z][A-Z][A-Z][A-Z][0-9][0-9][0-9]') CONSTRAINT Check_Code check (Code LIKE '[^A-Z]{4}[0-9]{3}')

But simply get the error:

insert into Table1 (Code) values ('HELP555') Error report: SQL Error: ORA-02290: check constraint (BOB.TABLE1_CHECK_CODE) violated 02290. 00000 - "check constraint (%s.%s) violated" *Cause: The values being inserted do not satisfy the named check *Action: do not insert values that violate the constraint.

Sathyajith Bhat · Accepted Answer · 2013-08-08 10:14:33Z

The regular expressions are not right, and the way you've used is pretty cumbersome.

Instead, you can opt to use POSIX character classes. In your specific case, it will be like so:

[[:alpha:]]{4}[[:digit:]]{3}

so the constraint check is

CONSTRAINT Code_Check check (REGEXP_LIKE(Paper_Code,'[[:alpha:]]{4}[[:digit:]]{3}')

This checks that there are 4 alphabetic characters, followed by 3 numeric.

Example on SQL fiddle & more info available at regexp cheatsheet

Collectives™ on Stack Overflow

Oracle: Check constraint for both alphabetical and numerical characters in varchar2

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related