TSQL LEN function returns a 1 when evaluating a zero-length integer

Question

I'm using SQL Server 2008.

I have found the LEN function does not return a zero when it evaluates an integer when the integer has no value - it returns a 1.

For example:

declare @int1 int
set @int1 = ''
select LEN(@int1)

A 1 is returned instead of a zero! But the integer is zero-length!

Why is this? Is it a bug?

Answer 1

An INT doesn't have a length. This is what you are actually doing...

• Create an INT variable
• Create a zero length string ''
• CAST that string to an INT 0
• Assign the result to the variable
• CAST the variable's value to a STRING '0'
• Caclulate the length of that string

The result of which is that your LEN function is not being called on '', but is infact being called on '0', which has a length of 1.

Answer 2

you should assign NULL to integers for them to mean "i have no value"

something like this:

declare @int1 int 
set @int1 = null
select @int1 
union
select LEN(isnull(cast(@int1 as varchar),''))

returns

NULL
0

Answer 3

int1 will be initialised as '0' so it is returning the correct result:

declare @int1 int set @int1 = '' 
select @int1
select LEN(@int1)

Returns:

0

1