Search This Blog

Thursday, February 2, 2017

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.




No comments:

Post a Comment