2

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.
Chris Doogan
  • 37
  • 1
  • 4

1 Answers1

2

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

Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134