LEN and DATALENGTH Function in SQL Server 2012

Today, I have provided an article showing you the Len and DataLength methods in SQL Server 2012. In this article, you will see the difference between the SQL Len and DataLength functions with an example. These are System functions. So let's have a look at a practical example of how to use the Len and Datalength functions in SQL Server. The example is developed in SQL Server using SQL Server Management Studio.

Len Function

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.

Syntax

LEN (string_expression) 

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 StringLength

Output

Problem with LEN Function

In the output above you get the length of the String. If you provide trailing spaces then it produces the same result.

For such situations you can use the "DATALENGTH" function.

DataLength Function

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.

Syntax

DATALENGTH (string_expression) 

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 StringLength

Output