Possibly incorrect query result

/* Test table */
create table test (c1 char(1), c2 varchar(1));
insert into test values ('','');

/* Query */
select
c1,
len(c1) len_c1,
c2,
len(c2) len_c2
from test

The result of the len(c1) expression is 0. I would expect the correct result to be 1, since "c1" is a fixed-length character string type and the values are right-padded with spaces to fit the defined length, in this case 1.

I'm using SQL Server 2005.

Regards,
Ole Willy Tuv

LEN

Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.

Change LEN to DATALENGTH and run this

select
c1,
datalength(c1) len_c1,
c2,
datalength(c2) len_c2
from test

select datalength(' '),len(' ')

Denis the SQL Menace

http://sqlservercode.blogspot.com/