T-SQL Len function not working as expected [duplicate]

Question

I'm working with Microsoft SQL Server (2008 R2 if that matters)

When I execute

select LEN('test')

I get 4 as expected

But now try this:

declare @s varchar
set @s='test'
select LEN(@s)

And the result is ... 1

How does it actually work?

Answer 1

That is because if you were to

SELECT @s 

it would return

t

only. You need to specify the length.

From char and varchar (Transact-SQL)

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.

SQL Fiddle DEMO

Answer 2

When you define this:

declare @s varchar

you get a varchar of exactly one character length .

There's absolutely nothing wrong with LEN!

What you need to do is specify an explicit length when you define your varchar variable

declare @s varchar(20)
set @s='test'
select LEN(@s)

Now you should get back 4 from LEN ...

Note: it is a recommend best practice to always specify a length when you use varchar - otherwise you'll run into these kind of surprises. ....