stringtypes.sql

derby database source code.good for you.

--
-- this test shows the current supported char, varchar, and long varchar
-- functionality
--


-- create a table with null and non-null char columns of different lengths
create table ct (c1 char, c2 char(5) not null, c3 char(30) default null);

-- insert some rows

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

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

-- now try some values that are longer than the columns, where the excess
-- characters are blanks
insert into ct 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 ct values ('44', '4', '4');
insert into ct values ('5', '555555', '5');
insert into ct values ('6', '66666', '6666666666666666666666666666666');

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

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

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

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

-- now create a table with varchar columns
create table vt (c1 varchar(1), c2 varchar(5) not null, c3 varchar(30) default null);