SQL DATALENGTH Function

SQL  DATALENGTH function is used to return the number of bytes used to represent a given string.

SYNTAX

DATALENGTH(string)

Lets look at an example of Datalength function in SQL.

DECLARE @Stringtxt VARCHAR(50) 
SET @Stringtxt = 'microsoft sql server' 
SELECT @Stringtxt AS stringtxt, DATALENGTH (@Stringtxt) AS 'DataLength'

As you can see, the string ‘microsoft sql server’ is of varchar datatype which uses one bytes per character so the DataLength function returns the length of string that is 20 *1 = 20 in bytes.

Lets take one more example ,  Here we have a string having three trailing spaces .

DECLARE @Stringtxt VARCHAR(50) 
SET @Stringtxt = 'microsoft sql server   ' 
SELECT @Stringtxt AS stringtxt ,DATALENGTH (@Stringtxt) AS 'DataLength'

As you can see, Datalength function returns string length that is 23 in bytes which includes a trailing spaces also. It is also a difference between Len() and Datalength. Length Function does not include trailing spaces while datalength function includes trailing spaces.

Lets look at an example of Datalength with string which are having nvarchar datatype.

DECLARE @Stringtxt NVARCHAR(50) 
SET @Stringtxt = 'microsoft sql server ' 
SELECT @Stringtxt AS stringtxt ,DATALENGTH (@Stringtxt) AS 'DataLength'

As string having a datatype nvarchar which uses 2 bytes per character, so DATALENGTH() function returns  length of string as  20 * 2 = 40 bytes.

Also Read..

SQL Server Len() function

SQL Server VARCHAR VS NVARCHAR

 1,529 total views,  1 views today