SQL - DATELENGTH() Function

The SQL DATELENGTH() function returns the number of bytes used to represent any expression and also counts the leading and trailing spaces of the expression. This function may return a bigint or an int as a data type.

If the expression has an nvarchar(max), varbinary(max), or varchar(max) data type, this function returns bigint; otherwise, it returns int.

This function is very helpful when used with data types it can store variable length data such-as

  • image
  • ntext
  • nvarchar
  • text
  • varbinary
  • varchar

Syntax

following is the syntax of the SQL DATALENGTH() function −

SELECT DATALENGTH(expression);

expression − it takes the data types that we need to return the length for. If the expression is null, it returns null.

Example

In the following example, we are demonstrating the use of DATALENGTH() function by passing the simple expression.

Following is the query to find the length of given name −

SELECT DATALENGTH('Aman kumar') AS lengthOf_Name;

Output

Following is the output of the above query −

+----------------+
| lengthOf_Name  |
+----------------+
|             10 |
+----------------+

The following example returns the bytes of the used data types. This demonstrates how much data is required to represent the same value using different data types.

following is the query −

DECLARE @var_int int = 1
DECLARE @var_smallint smallint = 1
DECLARE @var_decimal decimal = 1
DECLARE @var_bigint bigint = 1
 
SELECT
   'int' datatype, DATALENGTH(@var_int) bytes
   UNION
SELECT
   'smallint' datatype, DATALENGTH(@var_smallint) bytes
   UNION
SELECT
   'decimal' datatype, DATALENGTH(@var_decimal) bytes
   UNION
SELECT
   'bigint' datatype, DATALENGTH(@var_bigint) bytes

Output

Following is the output of the above query −

+----------+--------+
| datatype |  bytes |
+----------+--------+
| bigint   |      8 |
+----------+--------+
| decimal  |      5 |
+----------+--------+
| int      |      4 |
+----------+--------+
| smallint |      2 |
+----------+--------+

Example

In the following example, we are passing a date and a word with some spaces inside the DATALENGTH() function to count the bytes of the given expression.

Following is the SQL query −

SELECT DATALENGTH(' tutorialspoint.com ') AS word_len, 
DATALENGTH('2023-02-01') AS date_len;

Output

following is the output of the above query −

+----------+----------+
| word_len | date_len |
+----------+----------+
|       20 |	   10 |
+----------+----------+

Example

In the following example, we are calculating the number of bytes in the salary and address columns of the customers table.

Let's create the customer table using the CREATE statement.−

CREATE TABLE customers(
ID INT NOT NULL, 
NAME VARCHAR(30) NOT NULL,
AGE INT NOT NULL, 
ADDRESS CHAR(30), 
SALARY DECIMAL(18, 2));

The table stores the ID, NAME, AGE, ADDRESS, and SALARY. Now we are inserting the 7 records in the customers table using the INSERT statement.

INSERT INTO customers VALUES(1, 'Ramesh', 32, 'Ahmedabad', 2000.00);
INSERT INTO customers VALUES(2, 'Khilan', 25, 'Delhi', 1500.00);
INSERT INTO customers VALUES(3, 'kaushik', 23, 'Kota', 2000.00);
INSERT INTO customers VALUES(4, 'Chaitali', 25, 'Mumbai', 6500.00);
INSERT INTO customers VALUES(5, 'Hardik', 27, 'Bhopal', 8500.00);
INSERT INTO customers VALUES(6, 'Komal', 22, 'MP', 4500.00);
INSERT INTO customers VALUES(7, 'Aman', 23, 'Ranchi', null);

Let's display the customers table using the SELECT statement.

SELECT * FROM customers;

Following is the customers table −

+----+----------+-----+-----------+---------+
| ID | NAME     | AGE | ADDRESS   | SALARY  |
+----+----------+-----+-----------+---------+
|  1 | Ramesh   |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan   |  25 | Delhi     | 1500.00 |
|  3 | kaushik  |  23 | Kota      | 2000.00 |
|  4 | Chaitali |  25 | Mumbai    | 6500.00 |
|  5 | Hardik   |  27 | Bhopal    | 8500.00 |
|  6 | Komal    |  22 | MP        | 4500.00 |
|  7 | Aman     |  23 | Ranchi    |    NULL |
+----+----------+-----+-----------+---------+

Following query display the bytes of the particular column −

SELECT 
   DATALENGTH(ADDRESS) AS Address_Bytes,
   DATALENGTH(SALARY) AS Salary_Bytes
   FROM customers;

Output

following is the output of the above query −

+---------------+--------------+
| Address_Bytes | Salary_Bytes |
+---------------+--------------+
|            30 |            5 |
|            30 |            5 |
|            30 |            5 |
|            30 |            5 |
|            30 |            5 |
|            30 |            5 |
|            30 |            5 |
+---------------+--------------+
sql-datatype-functions.htm
Advertisements