DATALENGTH() AND COL_LENGTH() in SQL Server

DATALENGTH

 
DATALENGTH function returns the number of bytes used to represent any expression. DATALENGTH is especially very useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data.

You can use DATALENGTH function as:

DATALENGTH ( exp )

where exp is the expression of the any datatype

 

Example

Suppose you have an table tbl_employee:
 
tbl_employee

Employee IdEmpNameAgeDepartmentSalary
1Ankur28Sales2500.00
2David32Sales3000.00
3John30NULL2200.00
5Mohan35Production4300.00

 

The following sql query return the length of the EmpName and Department column in the above tbl_Employee table

SELECT datalength(EmpName) AS [len OF name],EmpName, 
       datalength(Department) AS [Len OF Department],Department 
       FROM tbl_employee

And see the output:

len of nameEmpNameLen of DepartmentDepartment
10Ankur10Sales
10David10Sales
8JohnNULLNULL
10Mohan20Production

from the above you can see the The DATALENGTH value of NULL is NULL, see the record for EmpName ‘John’.

COL_LENGTH

 

COL_LENGTH returns the defined length in bytes of a column. So this is very useful when you insert the data into the table and you don’t know about the defined length of the columns, in this case you can check the defined length of the column before inserting data.

the following is the syntax for the COL_LENGTH function:

COL_LENGTH ( ‘tableName’ , ‘columnName’ )
where tablename is the actual table name and columnname is the column name for which you want to determine the length.

Example

The following example shows the how to get the length of the EmpName column in bytes:

SELECT col_length('tbl_employee','EmpName') AS 'nvarchar(50)'

Output will be :

nvarchar(50)
100

if the column is defined as varchar(max) then COL_LENGTH returns -1, and if you have not grant permission for the database then it returns NULL.

One thought on “DATALENGTH() AND COL_LENGTH() in SQL Server”

Comments are closed.