/* This is a bit of SQL code to demonstrate the behavior of spaces
** and nulls in char and varchar columns.  This is best viewed in
** a graphical browser...to demonstrate the differences.
*/

/* Oracle version created 11/16/04 to demonstrate a point */

---
drop table spaces;
create table spaces
    (cnot char(5) not null, 
    cnull char(5) null,  
    vnot varchar(5) not null, 
    vnull varchar(5) null, 
    v2not varchar2(5) not null,
    v2null varchar2(5) null, 
    explanation varchar2(50) not null) ;
  
insert into spaces values ('a', 'b', 'c', 'd', 'e', 'f', 'Chars get space padded, null or not null') ;
insert into spaces values ('1    ', '2    ', '3    ', '4    ', '5    ', '6    ', 'No truncation of trailing blanks') ;
insert into spaces values ('    g', '    h', '    i', '    j', '    k', '    l','leading blanks, no change') ;
insert into spaces values ('   u ', '   v ', '   w ', '   x ', '   y ','   z ','Again, no truncation of trailing blanks') ;
insert into spaces values (' ', '', ' ', '', ' ','','empty string equivalent to null for nullables...' ) ;
insert into spaces values (' ', ' ', ' ', ' ', ' ',' ', 'inserting one space; note chars get 5' ) ;
insert into spaces values ('     ', '     ', '     ', '     ','     ','     ', 'inserting 5 spaces; all preserved' ) ;
insert into spaces values (' ',NULL,' ',NULL,' ',NULL,'nulls inserted where possible');
commit;

set pagesize 9999
set linesize 120
select '[' || cnot || ']' as cnot, 
       '[' || cnull || ']' as cnull, 
       '[' || vnot || ']' as vnot, 
       '[' || vnull || ']' as vnull,  
       '[' || v2not || ']' as v2not, 
       '[' || v2null || ']' as v2null, 
    explanation from spaces ;

drop table spaces;

-----------

/* this bit demonstrates what happens when you use a 'not in'
** clause w/ null values, and the behavior of using 'is null' versus 
** '= null' and the ansinull option
*/

drop table notintest;
create table notintest
(col1 int, col2 varchar2(1) null, col3 char(1) null, col4 varchar(1));

insert into notintest values (1,'a','a','a');
insert into notintest values (2,'b','b','b');
insert into notintest values (3,'c','c','c');
insert into notintest values (4,'d','d','d');
insert into notintest values (5,'e','e','e');
insert into notintest values (6,'','','');
insert into notintest values (7, NULL,NULL,NULL);

/* first test the varchar2() behavior */
select col1,col2 from notintest
--where col2 not in ('a','b','c') -- returns 4,5, but not 6 or 7; '' is equiv to NULL in oracle's varchar2()
where not exists (select null from notintest where col2 = 'a' or col2 ='b' or col2='c')
--where col2 is NULL              -- returns 6 and 7 for the same reasons as above
--where col2 = NULL -- returns nothing; there is no "NULL" in =, <, >, etc operations.
--where col2 <> 'a'                 -- returns 2,3,4,5,, NOT 6 and 7 b/c 6 and 7 are NULL.
--where col2 is NULL -- gets 6 and 7.


/* now testing the char() behavior; note that char() defined as NULL behaves the EXACT same way
as varchar2().  Only when you define char() as not null do spaces get preserved */
select col1,col3 from notintest
--where col3 not in ('a','b','c') -- returns 4,5, but not 6 or 7; '' is equiv to NULL in oracle's varchar2()
--where col3 is NULL              -- returns 6 and 7 for the same reasons as above
--where col3 = NULL -- returns nothing; there is no "NULL" in =, <, >, etc operations.
--where col3 <> 'a'                 -- returns 2,3,4,5,, NOT 6 and 7 b/c 6 and 7 are NULL.
where col3 is NULL -- gets 6 and 7.

/* now test the varchar() function; same behavior as varchar2()  */
select col1,col4 from notintest
--where col4 not in ('a','b','c') -- returns 4,5, but not 6 or 7; '' is equiv to NULL in oracle's varchar2()
--where col4 is NULL              -- returns 6 and 7 for the same reasons as above
--where col4 = NULL -- returns nothing; there is no "NULL" in =, <, >, etc operations.
--where col4 <> 'a'                 -- returns 2,3,4,5,, NOT 6 and 7 b/c 6 and 7 are NULL.
where col4 is NULL -- gets 6 and 7.

drop table notintest;