The SQL DATALENGTH is used to return the number of bytes used to represent a specified expression. In this article we will show you, How to use/write DATALENGTH in SQL Server with example.
SQL DATALENGTH Function Syntax
The basic syntax of DATALENGTH in SQL Server is as shown below:
|
1 2 |
SELECT DATALENGTH (String_Expression) FROM [Source] |
This function is very useful for VARCHAR, NVARCHAR, VARBINARY, TEXT, NTEXT, and IMAGE Data Types because these data types will store the data length.
For this example, We are going to use the below shown data
SQL DATALENGTHÂ Example 1
The DATALENGTH Function is used toreturn the number of bytes used to represent a given expression. The following query will show multiple ways to use DATALENGTH function.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 |
DECLARE @String_Exp VARCHAR(50) SET @String_Exp = 'Learn SQL Server' DECLARE @Str_Exp NVARCHAR(50) SET @Str_Exp = 'Learn SQL Server' SELECT DATALENGTH (@String_Exp) AS 'Data Length' SELECT DATALENGTH (@Str_Exp) AS 'Data Length' --Finding Data Length directly SELECT DATALENGTH ('SQL Server Tutorial') AS 'Data Length' SELECT DATALENGTH ('SQL Server') AS 'Data Length' |
OUTPUT
ANALYSIS
Below lines of code is used to declare string variable of Type Varchar, and Nvarchar, and assigning the same data to both the variables.
|
1 2 3 4 5 |
DECLARE @String_Exp VARCHAR(50) SET @String_Exp = 'Learn SQL Server' DECLARE @Str_Exp VARCHAR(50) SET @Str_Exp = 'Learn SQL Server' |
In the below statement, We used DATALENGTH function to find the data length of both the variables. We also assigned new name to that result as ‘Data Length’ using SQL ALIAS Column.
|
1 2 |
SELECT DATALENGTH (@String_Exp) AS 'Data Length' SELECT DATALENGTH (@Str_Exp) AS 'Data Length' |
In the next line, We used the DATALENGTH Function directly on the string.
SQL DATALENGTH Function Example 2
The DATALENGTH function also allows you to find the number of bytes in a column. In this example, We are going to find the Data length of a Department Name, and Email columns.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 |
USE [SQL Tutorial] GO SELECT [FirstName] ,[LastName] ,[DepartmentName] ,DATALENGTH([DepartmentName]) AS [Dept Data Length] ,[Email] ,DATALENGTH([Email]) AS [Email Data Length] FROM [Employe] |
OUTPUT
Thank You for Visiting Our Blog
Content is very good.Easily understandable with examples.