SQL DATALENGTH() Function

The SQL DATALENGTH() function returns the number of bytes used to represent any expression. It is useful for determining the actual storage size of data in bytes, particularly for binary and variable-length data types like VARCHAR, VARBINARY, and TEXT.

Unlike LEN or CHAR_LENGTH, which return the number of characters, DATALENGTH provides the size in bytes, making it essential for understanding data storage requirements.

The DATALENGTH function is primarily supported in SQL Server.

In this tutorial, we will go through SQL DATALENGTH() String function, its syntax, and how to use this function in SQL statements for string operations, with the help of well detailed examples.

Syntax of SQL DATALENGTH Function

The basic syntax of the SQL DATALENGTH function is:

</>
Copy
DATALENGTH(expression);

Each part of this syntax has a specific purpose:

  • expression: The string or binary data to measure. This can be a column, variable, or literal value. The function returns the size of the data in bytes.

Setup for Examples: Creating the Database and Table

We’ll create a sample documents table to demonstrate the DATALENGTH function examples, which will allow us to explore how different data occupies storage space in bytes.

1. First, create a new database called document_store:

</>
Copy
CREATE DATABASE document_store;

2. Select the document_store database to work with:

</>
Copy
USE document_store;

3. Create a table named documents with columns doc_id, title, and content:

</>
Copy
CREATE TABLE documents (
    doc_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100),
    content TEXT
);

4. Insert sample data into the documents table with various title and content lengths:

</>
Copy
INSERT INTO documents (title, content)
VALUES 
('Introduction to SQL', 'SQL is a standard language for accessing and managing databases.'),
('Advanced SQL Techniques', 'This document contains advanced techniques for optimizing SQL queries.'),
('Short Note', 'SQL basics.'),
('Extensive SQL Guide', REPLICATE('This guide covers SQL in detail. ', 10));

With this setup complete, wecan run the DATALENGTH function examples to test and view results in the documents table.

Examples for Using DATALENGTH in SQL Queries

We’ll go through examples demonstrating the DATALENGTH function in SQL, using sample data from a documents table with fields doc_id, title, and content.

1. Finding the Data Length of a String Column

To find the storage size of each title in bytes from the documents table:

</>
Copy
SELECT title, DATALENGTH(title) AS title_length
FROM documents;

This query returns the length in bytes of each document title. For variable-length types like VARCHAR, DATALENGTH provides the actual storage size.

2. Using DATALENGTH on a NULL Value

To check the result of DATALENGTH when applied to a NULL value:

</>
Copy
SELECT DATALENGTH(NULL) AS length_of_null;

This query returns NULL because DATALENGTH on a NULL value returns NULL instead of 0.

3. Using DATALENGTH with Different Data Types

To find the byte size of different types of data, such as integers and text, in a table:

</>
Copy
SELECT doc_id, DATALENGTH(doc_id) AS id_length, DATALENGTH(content) AS content_length
FROM documents;

This query returns the size of doc_id (typically fixed) and content (variable) in bytes, providing insight into how different data types occupy storage.

4. Using DATALENGTH to Filter Results

To retrieve documents where the content length exceeds a specific byte size, for example, 100 bytes:

</>
Copy
SELECT title, DATALENGTH(content) AS content_length
FROM documents
WHERE DATALENGTH(content) > 100;

This query filters documents to show only those with content larger than 100 bytes.

FAQs for SQL DATALENGTH

1. What does the SQL DATALENGTH function do?

The DATALENGTH function returns the number of bytes used to store data in a specified expression, including both fixed and variable-length data types.

2. How is DATALENGTH different from LEN?

DATALENGTH returns the byte count of data, while LEN or CHAR_LENGTH returns the character count. For example, in multibyte character sets, DATALENGTH may return a higher value than LEN.

3. What happens if DATALENGTH is used on a NULL value?

If DATALENGTH is used on a NULL value, it returns NULL.

4. Can DATALENGTH be used with all data types?

Yes, DATALENGTH works with all data types, including VARCHAR, TEXT, INT, and VARBINARY.

5. Is DATALENGTH supported in MySQL?

No, DATALENGTH is not supported in MySQL. An equivalent function for string lengths in bytes in MySQL is OCTET_LENGTH.