Home > Functions and DMVs, SQL Queries, SQL Server, T-SQL > Difference between Datalength() and Len() functions in SQL server

Difference between Datalength() and Len() functions in SQL server

September 17, 2009 Leave a comment Go to comments

Often the functions Datalength() and Len() lead to confusion, Both looks the same and returns length. But “Length of what ” does it return ?

Both the function are string functions used in SQL. The function “DATALENGTH()” returns the number of bytes used to represent any expression/ string. It is especially useful in determining the space used by the string. Sample queries are as given below.

SELECT DATALENGTH(CAST('HELLO' AS  VARCHAR(5)))
SELECT DATALENGTH(CAST('HELLO' AS  NVARCHAR(5)))

The above queries return different value. The first one returns “5” whereas the second returns “10”. This is because of the variation in the datatype. The first string is of type “varchar” whereas second is “nvarchar”. Here the data bytes used is different.

The function “LEN()” returns the number of characters, instead of the number of bytes, of the specified string expression. it excludes trailing blanks. On running the below queries

SELECT LEN(CAST('HELLO' AS  VARCHAR(5)))
SELECT LEN(CAST('HELLO' AS  NVARCHAR(5)))

You will get the o/p as “5” for both the queries, the length of the string.

Another difference is that, The len() truncate trailing spaces and then calculates the length, where as datalength() does not remove the trailing spaces.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: