/* 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.
*/

---

create table tempdb..spaces (cnot char(5) not null, 
    cnull char(5) null,  
    vnot varchar(5) not null, 
        vnull varchar(5) null,  
    explanation varchar(25) not null) 
  
insert tempdb..spaces values ("a", "b", "c", "d",    "pads char-not-null only") 
insert tempdb..spaces values ("1    ", "2    ", "3    ",     "4    ", "truncates trailing blanks") 
insert tempdb..spaces values ("    e", "    f", "    g",     "    h", "leading blanks, no change") 
insert tempdb..spaces values ("   w ", "   x ", "   y ",    "   z ", "truncates trailing blanks") 
insert tempdb..spaces values ("", "", "", "",     "empty string equals space" ) 
insert tempdb..spaces values (" ", " ", " ", " ",     "inserting one space" ) 
insert tempdb..spaces values ("     ", "     ", "     ", "     ",     "inserting 5 spaces" ) 
insert tempdb..spaces values ("",NULL,"",NULL,"nulls inserted where possible")

go
  
select "[" + cnot + "]", 
       "[" + cnull + "]", 
       "[" + vnot + "]", 
       "[" + vnull + "]",  
    explanation from tempdb..spaces 

go

drop table tempdb..spaces
go

-----------

/* 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
*/

create table tempdb..test
(col1 int, col2 varchar(15) null)
go

insert into tempdb..test values (1,"a")
insert into tempdb..test values (2,"b")
insert into tempdb..test values (3,"c")
insert into tempdb..test values (4,"d")
insert into tempdb..test values (5,"e")
insert into tempdb..test values (6,"")
insert into tempdb..test values (7, NULL)
go

set ansinull on
go

select * from tempdb..test
--where col2 not in ('a','b','c') -- returns 4,5,6 NOT 7
--where col2 is NULL              -- returns 7
--where col2 = NULL -- returns 7 IF ansinull is set to off, otherwise nothing
where col2 <> "a"                 -- returns 2,3,4,5,6 NOT 7
--or col2 is NULL 
go

drop table tempdb..test
go