467,185 Members | 1,238 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,185 developers. It's quick & easy.

check constraint


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 (

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

Jul 30 '07 #1
  • viewed: 4735
2 Replies
First of all, if the 15 characters must be digits, shouldn't you use a DECIMAL(15) data type instead?


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):
Expand|Select|Wrap|Line Numbers
  1. alter table sample add
  2. check(substr(char(decimal(substr(col1,1,15),15,0)),1,15) = substr(col1,1,15) and col1 like '%    ')
Good luck.
Jul 31 '07 #2
The following alternative avoids the SQL conversion error:

Expand|Select|Wrap|Line Numbers
  1. create table sample (col2 TIMESTAMP NOT NULL,
  2.                      col1 CHAR(19) NOT NULL,
  3.                      col3 CHAR(1) NOT NULL,
  4.                      primary key(col2,col1,col3));
  6. alter table sample add
  7.       check(translate(substr(col1,1,15),'X          ',' 0123456789') = ''
  8.         and substr(col1,16,4) = '');
  10. insert into sample values(current timestamp, '123456 89012345    ', 'X');
  11. insert into sample values(current timestamp, '123456B89012345    ', 'X');
  12. insert into sample values(current timestamp, '1234567890123456   ', 'X');
  13. insert into sample values(current timestamp, '123456789012345    ', 'Y');
Aug 2 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Hari Om | last post: by
1 post views Thread by Jagdip Singh | last post: by
reply views Thread by gnuoytr@rcn.com | last post: by
2 posts views Thread by Robert Fitzpatrick | last post: by
3 posts views Thread by Helen Wheels | last post: by
2 posts views Thread by joerg.sailer@weltbild.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.