LEN and DATALENGTH

LEN function is very commonly used function in t-sql. One thing to note in this function that is ignores the trailing blanks;

Declare @v VarChar(5)

Set @v = 'ati '

Select Len(@v)

Same goes for CHAR and NVARCHAR.

To overcome this, use and character at the end and minus 1 from the length J

Declare @v VarChar(5)

Set @v = 'ati '

Set @v = @v + '?'

Select Len(@v)-1

Some people might use 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)

Thanks for reading.