Skip to content
Advertisement

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

Advertisement

Answer

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.

1 People found this is helpful
Advertisement