LEN and DATALENGTH Function in SQL Server

The LEN function is a System function. The System functions can never be created by the user. They are pre-defined functions. The Len() function returns the length of the result of the string expression excluding trailing spaces. The Datalength() function does not return the length of a string, instead it returns the number of bytes the data occupies. In other words it returns the number of bytes used to represent an expression. Without trailing spaces it will produce the same result as the LEN function.

Len Function

Example

Declare @string varchar(20)

Declare @TraillingSpacestring varchar(40)

set @string ='rohatash' -- Without Trailling space

set @TraillingSpacestring ='rohatash ' -- Trailling Space

select LEN (@string) as StringLength

select LEN (@TraillingSpacestring) as TraillingSpacestringStringLength

Output

 StringLength 8
TraillingSpacestringStringLength  8

DataLength Function

Example

Declare @string varchar(20)

Declare @TraillingSpacestring varchar(40)

set @string ='rohatash'

set @TraillingSpacestring ='rohatash ' -- Trailling Space

select LEN (@string) as StringLength

select DATALENGTH (@TraillingSpacestring) as TraillingSpacestringStringLength

 

Output

 

StringLength 8

TraillingSpacestringStringLength 9