CODEWITHSUNDEEP
sqlregexoracle-databasesqldatatypescheck-constraints
Andes Lam
Andes Lam

Reputation: 202

Oracle SQL Developer limit number of character of datatype char(5)

I was tasked to implement a table with a variable groupcode. There are several requirements.

  1. char(5)
  2. 2 or 4 uppercase letters plus 1 digit whose value is between 1 and 4 (e.g., AA1, AABB1)
  3. Any input other violating point 1 and 2 should be banned

The only thing I can come up with is regexp_like but my efforts are in vain, for example

create table Test(
groupcode char(5) check(
regexp_like(groupcode, '^[A-Z]{2}[0-9]$', 'c') 
)
);

INSERT INTO Test(groupcode) VALUES ('AA1');

I inserted this but it keep telling me there's some violation, in which I don't understand.

I specify ^ and $ because I don't want something like 'bbAA1' or 'AA1bb' appear.

I am also open to non-regex solution. After all the work in this afternoon, I am starting to doubt my approach.

Edit--To Tim:enter image description here

Upvotes: 2

Views: 718

Answers (2)

user330315
user330315

Reputation:

Your regex ^[A-Z]{2}[0-9]$ only allows (exactly) three characters. But a column defined as char(5) gets padded with spaces if you provide less than three, so in reality 'AA1' will be stored as 'AA1 ' which violates your regex.

Use char(3) instead.

Better yet, never use char at all (you just experienced one of the reasons why), use varchar(5) or varchar(3) both will work with your regex.

2 or 4 uppercase letters plus 1 digit whose value is between 1 and 4

That's not what your regex does. [A-Z]{2} is exactly two uppercase letters. If you want to allow patterns of different lengths there is no way you can use char(5) unless you also include the spaces in the regex:

create table test
(
  groupcode char(5) check(
      regexp_like(groupcode, '(^[A-Z]{2}[0-9]  $)|(^[A-Z]{4}[0-9]$)', 'c'))
);

The above regex allows two uppercase characters followed by on digit and two spaces or four uppercase characters followed by one digit.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522541

I would suggest:

CREATE TABLE Test (
    groupcode VARCHAR(5),
    CONSTRAINT (CHECK(REGEXP_LIKE(groupcode, '^[A-Z]{2}([A-Z]{2})?[1-4]$', 'c')))
);

Here is an explanation of the regex pattern:

^                from the start of the column
    [A-Z]{2}     match AA, AB, BB, etc.
    ([A-Z]{2})?  then match another optional AA, AB, BB, etc.
    [1-4]        match 1-4
$                end of the column

Demo

Late Edit: The @Horse answer correctly points out that you should use VARCHAR here. But your regex also had a problem, which I fixed.

Upvotes: 0

Related Questions