Share via

T-SQL: LEN and DATALENGTH

  • Article

LEN function is a very commonly used function in T-SQL. One thing to note in this function is it ignores trailing blanks.

Declare @v
VarChar(5)   
Set @v = 'ati '  
Select Len(@v)

Same goes for CHAR and NVARCHAR. To overcome this, use character at the end and minus 1 from the length.

Declare @v
VarChar(5)   
Set @v = 'ati '
Set @v = @v + '?'    Select Len(@v)-1

 

Some people might use the DATALENGTH function.

Declare  @v 
VarChar(5)
Set @v = 'ati '
Select   DATALENGTH(@v)

    

But be alert as DATALENGTH function counts the bytes, not the length. If you change the data type of @v from varchar to nvarchar, the result will change.

Declare  @v   NVarChar(5)
Set @v = 'ati '    Select   DATALENGTH(@v)