CODEWITHSUNDEEP
sqldatabaseoracle11gconstraints
Kairan
Kairan

Reputation: 5542

Oracle Column Constraint on char(4) field to not accept insert of 3 char value

Our issue is that we have XLS that has for example '0112' in a "Text" field

Then someone transfers this sheet (not sure if copy/paste) into another spreadsheet and they lose the 0, and the CSV has '112 ' (hard to see but there is a space after the 112)

Then some java code reads the csv and inserts the values into database table (Oracle 11g) and the field is type char(4) and so it seems to append a space to the end and we end up with '112 '.

Is there some way to add some constraint to the Oracle table/column to not automatically pad or add the extra space, we would prefer that we receive some error trying to insert 112 into a char(4)

Upvotes: 0

Views: 667

Answers (1)

Boneist
Boneist

Reputation: 23588

You could always try adding a constraint that checks the length of your string is 4, after having stripped off any spaces:

create table test1 (col1 char(4));

alter table test1 add constraint t1_chk check (length(trim(col1)) = 4);

-- the following inserts ok
insert into test1 values ('1234');

-- the following fails
insert into test1 values ('112 ');

commit;

Upvotes: 1

Related Questions