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