stringtypes.sql

derby database source code.good for you.

-- insert some rows

-- first, try values that fill each column with non-blanks
insert into vt values ('1', '11111', '111111111111111111111111111111');

-- now try some values that are shorter than the columns
insert into vt values ('', '22', '222');

-- now try some values that are longer than the columns, where the excess
-- characters are blanks
insert into vt values ('3         ', '33333      ', '333333333333333333333333333333          ');

-- now try some values that are longer than the columns, where the excess
-- characters are non-blanks.  These should get errors
insert into vt values ('44', '4', '4');
insert into vt values ('5', '555555', '5');
insert into vt values ('6', '66666', '6666666666666666666666666666666');

-- now try inserting some nulls, first in columns that accept them
insert into vt values (null, '77777', null);

-- now try inserting nulls into columns that don't accept them
insert into vt values ('8', null, '8');

-- now check the rows that made it into the table successfully
select * from vt;

-- now try the char_length function on the columns
select {fn length(c1)}, {fn length(c2)}, {fn length(c3)} from vt;

-- now create a table with long varchar columns
create table lvt (c1 long varchar, c2 long varchar not null, c3 long varchar default null);

-- insert some rows
-- first, some short values
insert into lvt values ('1', '11', '111');

-- now some values with trailing blanks (which should be preserved)
insert into lvt values ('2 ', '22  ', '222   ');