The following example creates a table named spaces that has both fixed- and variable-length character columns:
create table 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 spaces values ("a", "b", "c", "d", "pads char-not-null only")
insert spaces values ("1 ", "2 ", "3 ", "4 ", "truncates trailing blanks")
insert spaces values (" e", " f", " g", " h", "leading blanks, no change")
insert spaces values (" w ", " x ", " y ", " z ", "truncates trailing blanks")
insert spaces values ("", "", "", "", "empty string equals space")
select "[" + cnot + "]",
"[" + cnull + "]",
"[" + vnot + "]",
"[" + vnull + "]",
explanation from spaces
explanation ------- ------- ------- ------- -------------------- [a ] [b] [c] [d] pads char-not-null only [1 ] [2] [3] [4] truncates trailing blanks [ e] [ f] [ g] [ h] leading blanks, no change [ w ] [ x] [ y] [ z] truncates trailing blanks [ ] [ ] [ ] [ ] empty string equals space (5 rows affected)
This example illustrates how the column’s datatype and null type interact to determine how blank spaces are treated:
Only char not null and nchar not null columns are padded to the full width of the column; char null columns are treated like varchar and nchar null columns are treated like nvarchar.
Only unichar not null columns are padded to the full width of the column; unichar null columns are treated like univarchar.
Preceding blanks are not affected.
Trailing blanks are truncated except for char, unichar, and nchar not null columns.
The empty string (“ ”) is treated as a single space. In char, nchar, and unichar not null columns, the result is a column-length field of spaces.
