Satyaprakash Samantaray
Difference between LEN() and DATALENGTH() in sql server ?
By Satyaprakash Samantaray in SQL Server on Mar 07 2017
  • Siva Tiyyagura
    Apr, 2017 5

    Len will give you how many characters are there in data(Any data type) whereas DataLenth() will give you how much memory required to store the same data(in Bytes). We can observe the difference between them when checked with data types other than char/string.Len(2) = 1 Len(20) = 2 DataLength(2) / DataLength(20) / DataLength(200) = 4 .Equivalents in C#. Len() = string.Length() DataLength() = SizeOf()Try with other data types.

    • 6
  • Shalu Shalini
    Apr, 2017 1

    DATALEN will return the number of bytes that are used to store the value LEN will return the number of characters in a string. Since a string can use single or double-byte characters, this differs from DATALENGTH in that you will always get 1, no matter how long a single character is

    • 3
  • Harinder Kumar
    Oct, 2017 16

    Yes the LEN() will return the length of data(count of characters) whereas the DATALENGTH() will return the length of memory to store that data, the straight forward example of that isDeclare @data nvarchar(20) set @data='abc' select LEN(@data) as _LEN , DATALENGTH(@data) as _DATALENGTH you will get output as _LEN=3(length of data) and _DATALENGTH=6(length of memory to store @data), _DATALENGTH=6 because the nvarchar takes 2 bytes per char. Hope that will help you :)

    • 1
  • Harinder Kumar
    Oct, 2017 16

    Yes the LEN() will return the length of data(count of characters) whereas the DATALENGTH() will return the length of memory to store that data, the straight forward example of that isDeclare @data nvarchar(20) set @data='abc' select LEN(@data) as _LEN , DATALENGTH(@data) as _DATALENGTH you will get output as _LEN=3(length of data) and _DATALENGTH=6(length of memory to store @data), _DATALENGTH=6 because the nvarchar takes 2 bytes per char. Hope that will help you :)

    • 1
  • Sagar Sawale
    Jul, 2017 14

    Len is count characters with skip the blank space after character e.g. LEN('satya ') o/p = 5. But it will count blank space before start the string e.g. LEN(' satya') o/p = 6. DataLength is count blank spaces form both side gives output e.g. DATALENGTH(' satya ') o/p = 7.

    • 1
  • Srikanth Gogineni
    May, 2017 5

    Rlease refer http://sqlhints.com/2015/07/18/difference-between-len-and-datalength-functions-in-sql-server/

    • 1
  • Rohanjoy Bhattacharya
    Mar, 2017 31

    Check out these two links- https://docs.microsoft.com/en-us/sql/t-sql/functions/len-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/functions/datalength-transact-sql

    • 1
  • Paul
    Mar, 2017 29

    Refer this link for more information: http://sqlhints.com/2015/07/18/difference-between-len-and-datalength-functions-in-sql-server/

    • 1
  • Satyaprakash Samantaray
    Mar, 2017 7

    LEN() counts no. of characters and it skips the blank spaces.e.g : SELECT LEN('Satya ') AS 'LEN o/p : 5DATALENGTH() counts no. of characters including the blank spaces.e.g : SELECT DATALENGTH('Satya ') AS 'DATALENGTH' o/p : 10

    • 1
  • Amar Srivastava
    Sep, 2017 18

    In a very simple way both will tell you the length that you specify in that functionSELECT LEN('Testdata') AS 'LEN'LEN ------ 8SELECT DATALENGTH('Testdata') AS 'DATALENGTH'LEN ------ 8however the difference comes where we found the trailing spaces likeSELECT LEN('Testdata ') AS 'LEN'LEN ------ 8SELECT LEN('Testdata ') AS 'LEN'LEN ------ 18hope this will help..

    • 0
  • Jothish Kumar
    Sep, 2017 14

    http://sqlhints.com/2015/07/18/difference-between-len-and-datalength-functions-in-sql-server/

    • 0
  • Jothish Kumar
    Sep, 2017 14

    1.The LEN() Sql Server function returns the number of characters in the specified string expression where the DATALENGTH() Sql Server function returns the number of bytes used/required to represent an expression. 2. bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise, in and bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise int.

    • 0
  • Krishna Lankireddy
    Sep, 2017 7

    Len(): Gives length of expression or count of chars. DATALength(): it gives number of bytes I an expression.

    • 0
  • Rajkiran Swain
    Sep, 2017 1

    http://sqlhints.com/2015/07/18/difference-between-len-and-datalength-functions-in-sql-server/

    • 0
  • Rajkiran Swain
    Jun, 2017 7

    LEN() DATALENGTH() DEFINITION The LEN() Sql Server function returns the number of characters in the specified string expression The DATALENGTH() Sql Server function returns the number of bytes used/required to represent an expression SYNTAX LEN ( string_expression ) DATALENGTH ( expression ) INPUT PARAMETER Input parameter is a string expression, it can be a constant or variable or column of character or binary data Input parameter is an expression of any data type RETURN TYPE bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise, int bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise int EXAMPLE SELECT LEN('Shree') AS 'LEN' RESULT: LENSELECT DATALENGTH('Shree') AS 'DATALENGTH' RESULT: DATALENGTH

    • 0
  • Raj Kumar
    Jun, 2017 6

    LEN() will return the number of characters in a string. Since a string can use single or double-byte characters, this differs from DATALENGTH in that you will always get 1, no matter how long a single character is.DATALEN will return the number of bytes that are used to store the value.

    • 0
  • Zahid Mustafa
    May, 2017 17

    If you want to just get number of characters excluding blanks you would use LEN() function, while in all other cases DATALENGTH()

    • 0
  • Rajkiran Swain
    May, 2017 15

    LEN() ->The LEN() Sql Server function returns the number of characters in the specified string expressionThe DATALENGTH() Sql Server function returns the number of bytes used/required to represent an expressionLEN ( string_expression )DATALENGTH ( expression )

    • 0
  • Hitendra kumbhar
    May, 2017 9

    Len() function means find out how many characters in string for any datatypes and Data length() is that to allow how much data are store inside data column(filed).

    • 0
  • Subash
    Apr, 2017 26

    LEN() function returns character it doesnot take whitespace DATALENGTH()function returns whitespace also eg: SELECT LEN('SUBASH ') output 6 SELECT DATALENGTH('SUBASH ') output 7

    • 0
  • Bidya Mishra
    Apr, 2017 19

    The LEN() Sql Server function returns the number of characters in the specified string expression The DATALENGTH() Sql Server function returns the number of bytes used/required to represent an expression

    • 0
  • Nihar Narendra
    Apr, 2017 13

    The LEN() and DATALENGTH() functions are there for completely different purpose. But it often confuses many in identifying which one to use when. The aim of this article is to clear all these confusions by presenting a comparative analysis of LEN() and DATALENGTH() Sql Serer functions.

    • 0


Most Popular Job Functions


MOST LIKED QUESTIONS