check constraint

[vidhyat]

Hi,

I need to create a table in db2 with 3 columns say col1,col2,col3 in db2.
For col1 [datatype is char(19)] i need to add a check constraint to check whether the first 15 characters are numbers & remaining 4 characters are blank spaces. i tried it in the following way,but its not working.

create table sample (

col2 TIMESTAMP NOT NULL,
col1 CHAR(19) NOT NULL CONSTRAINT Acc_Num_Frmt LIKE '[0-9]* ',
col3 CHAR(1) NOT NULL,
primary key(col2,col1,col3)

);

can any of u suggest me a solution ASAP

Thanks,
Vidhya

[Chrisk]

First of all, if the 15 characters must be digits, shouldn't you use a DECIMAL(15) data type instead?

However...

Add a CHECK condition to the table which converts the digits to a DECIMAL(15) and back to a CHAR(15). Then compare the resulting string to the original. You will get a conversion SQL error if they are not digits.

It is not trivial (I think):

alter table sample add
check(substr(char(decimal(substr(col1,1,15),15,0)),1,15) = substr(col1,1,15) and col1 like '%    ')
 

Good luck.

[cburnett]

The following alternative avoids the SQL conversion error:

create table sample (col2 TIMESTAMP NOT NULL,
                     col1 CHAR(19) NOT NULL,
                     col3 CHAR(1) NOT NULL,
                     primary key(col2,col1,col3));
 
alter table sample add
      check(translate(substr(col1,1,15),'X          ',' 0123456789') = ''
        and substr(col1,16,4) = '');
 
insert into sample values(current timestamp, '123456 89012345    ', 'X');
insert into sample values(current timestamp, '123456B89012345    ', 'X');
insert into sample values(current timestamp, '1234567890123456   ', 'X');
insert into sample values(current timestamp, '123456789012345    ', 'Y');