CODEWITHSUNDEEP
sqloracle-databaseconstraints
user1800566
user1800566

Reputation: 3

SQL CHECK CONSTRAINT VARCHAR

create table table1
(
  column1 varchar2(8)
  check constraint column1_ch check ........
);

How do I do a check for a data that the first 4 char is a specific set of alphabets while the last 4 is numbers? and as well as a range of values.

examples, data can be ABCD2121, ABCD1111.

range - ABCD0001 to ABCD9999

So 'ABCD' is fixed while the numbers are changing.

I've foudn online about using '[]" to define the numbers but i'm not able to integrate it into my constraint.

Thanks

Upvotes: 0

Views: 3537

Answers (3)

Alexander
Alexander

Reputation: 2318

Your check condition should be as follows:

(column1 LIKE 'ABCD[0-9][0-9][0-9][1-9]')

Edit: Modified to use a set prefix vs. a range for the alpha characters.

Here's a solution using Microsoft SQL Server that illistrates this:

DECLARE @MyTable TABLE
(column1 varchar(8) check (column1 LIKE 'ABCD[0-9][0-9][0-9][1-9]'))

INSERT INTO @MyTable (column1)
       SELECT 'ABCD0000'
 UNION SELECT 'ABCD2121'
 UNION SELECT 'ABCD1111';

SELECT *
  FROM @MyTable;

INSERT INTO @MyTable (column1)
SELECT 'ABCD000A'; --<== Fails!

INSERT INTO @MyTable (column1)
SELECT 'ABCD221'; --<== Fails!

Upvotes: 0

user330315
user330315

Reputation:

The easiest way is to do this using a regular expression:

alter table table1
  add constraint chck_code check (regexp_like(column1, '(ABCD)[0-9]{4}') );

Upvotes: 1

Frank Schmitt
Frank Schmitt

Reputation: 30845

If you've got a fixed set of prefixes, use regexp_like and enumerate the prefix list:

alter table test_1 
add constraint chk_col1 check(regexp_like(column1, '(ABCD|EFGH)[0-9]{4}'));

This will allow ABCD and EFGH as prefixes, followed by exactly 4 digits.

Upvotes: 0

Related Questions