SQL Datalength() | What is Datalength in SQL Server?

Summary: In this tutorial, you will learn SQL DATALENGTH() Function and how to use them in an SQL server to counts both leading and trailing spaces when calculating the length of the expression.

Definition and Usage

In SQL Server the DATALENGTH() function represent an expression which returns the number of bytes.

SQL Datalength() syntax

In this syntax:

• expression: It is required value, here, the data type to return the length for, furthermore, If there are the expression is NULL, then it will returns the NULL

SQL Datalength() Example

SELECT DATALENGTH('Tutorialscan');

Output:

12

In this case, there are 12 bytes in the string Tutorialscan.
Therefore, its a simple example. The results can look a lot different, which is depending on the data type.

In the above example, you can see that there are 12 bytes in the word Tutorialscan. This also happens to be exactly how many characters are in the string.

This SQL server DATALENGTH() function is very useful for NVARCHAR, VARCHAR, TEXT, NTEXT, VARBINARY and IMAGE data types because these data types will store the data length.
Therefore, let’s see the next below example, we will going to use the given below data.

SQL DATALENGTH Example 1

It Function basically used to return the number of bytes and used to represent a given expression.
There are the following given below query will show that multiple ways to use the this function.

DECLARE @String_Exp VARCHAR(50)
SET @String_Exp = 'Read SQL Server Tutorial' 

DECLARE @Str_Exp NVARCHAR(50)
SET @Str_Exp = 'Read SQL Server Tutorial' 
 
SELECT DATALENGTH (@String_Exp) AS 'Data Length' 
SELECT DATALENGTH (@Str_Exp) AS 'Data Length'
 
--Finding Data Length directly
SELECT DATALENGTH ('SQL Tutorial') AS 'Data Length'
SELECT DATALENGTH ('SQL Server') AS 'Data Length'

Output:

DATALENGTH ANALYSIS

Furthermore, there are the given below lines of code are used to declare the string variable of Data Type NVarchar and varchar, and assigning the same data to both the variables.

DECLARE @String_Exp VARCHAR(50) 
SET @String_Exp = 'Read SQL Server Tutorial' 

DECLARE @Str_Exp VARCHAR(50) 
SET @Str_Exp = 'Read SQL Server Tutorial'

as you can see in the below statement, We used the DATALENGTH function to determine the data length of both the variables. furthermore and also assigned a new name to that result as ‘Data Length’ using SQL ALIAS Column.

SELECT DATALENGTH (@String_Exp) AS 'Data Length' 
SELECT DATALENGTH (@Str_Exp) AS 'Data Length' 

Furthermore, In the next line, We used this Function directly on the string.

DATALENGTH Function Example 2

It function allows you to find the number of bytes in a column. therefore, we are going to find the Data length of a Position and Email columns.

USE [SQLDEMOEXAMPLE]
GO
SELECT [FirstName],
       [LastName],
       [Position],
       DATALENGTH([Position]) AS [Position Data Length],
       [Email],
       DATALENGTH([Email]) AS [Email Data Length]
 FROM [Employe]

Output: