SQL Server Tutorial SQL Server Advanced SQL Server Database SQL Server References

SQL Server DATALENGTH() Function



The SQL Server (Transact-SQL) DATALENGTH() function returns the number of bytes used to represent the specified string expression.

Note: To count the number of characters of the string expression, LEN() function can be used. Note that, it excludes trailing spaces in the length calculation.

Syntax

DATALENGTH(string)

Parameters

string Required. Specify the string expression.

Return Value

Returns the number of bytes used to represent the specified string.

Example 1:

The example below shows the usage of DATALENGTH() function.

SELECT DATALENGTH('12345');
Result: 5

SELECT DATALENGTH('ABCDE');
Result: 5

SELECT DATALENGTH(10);
Result: 4

SELECT DATALENGTH('AlphaCodingSkills');
Result: 17

SELECT DATALENGTH('Alpha Coding Skills');
Result: 19

SELECT DATALENGTH(NULL);
Result: NULL

SELECT DATALENGTH('');
Result: 0

SELECT DATALENGTH(' ');
Result: 1

Example 2:

Consider a database table called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

The statement given below can be used to get the number of bytes used to represent the records of City column.

SELECT *, DATALENGTH(City) AS DATALENGTH_Value FROM Employee;

The query will produce the following result:

EmpIDNameCityAgeDATALENGTH_Value
1JohnLondon256
2MarryNew York248
3JoParis275
4KimAmsterdam309
5RameshNew Delhi289
6HuangBeijing287

❮ SQL Server Functions