Skip to main content

Return to Question

deleted 6 characters in body
Source Link
Chris Doogan
Chris Doogan
  • 37
  • 1
  • 4

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(Paper_CodeCode,'[^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.

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(Paper_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.

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

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

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(Paper_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.