SQL LEN()

The SQL LEN() function returns the length of a given string in terms of the number of characters it contains. This function is helpful for determining the size of text fields, checking for empty strings, and validating string lengths.

Note that LEN() does not count trailing spaces in SQL Server, which can be useful when working with fixed-length data. In MySQL, the equivalent function is CHAR_LENGTH().

In this tutorial, we will go through SQL LEN() 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 LEN() Function

The basic syntax of the SQL LEN() function is:

</>
Copy
LEN(string);

Each part of this syntax has a specific purpose:

  • string: The string whose length you want to determine. This can be a column, variable, or text literal.

The LEN() function returns an integer representing the number of characters in the string, excluding trailing spaces in SQL Server.

Setup for Examples: Creating the Database and Table

We’ll create a sample products table with fields product_name and description to demonstrate the LEN() function examples.

1. First, create a new database called store:

</>
Copy
CREATE DATABASE store;

2. Select the store database to work with:

</>
Copy
USE store;

3. Create a table named products with fields product_id, product_name, and description:

</>
Copy
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100),
    description TEXT
);

4. Insert sample data into the products table to use with the LEN() function examples:

</>
Copy
INSERT INTO products (product_name, description)
VALUES 
('Laptop', 'High-end gaming laptop'),
('Mouse', 'Ergonomic design'),
('Keyboard', 'Mechanical, RGB lighting'),
('Monitor', ''),
('Tablet', '');

With this setup complete, we can run the LEN() function examples to test and view results in the products table.

Examples: Using LEN() in SQL Queries

We’ll go through examples demonstrating the LEN() function in SQL, using sample data from a products table with fields product_name and description.

1. Finding the Length of Product Names

To get the length of each product_name in the products table:

</>
Copy
SELECT product_name, LEN(product_name) AS name_length
FROM products;

This query returns the number of characters in each product name, allowing you to understand the length of text stored in this field.

2. Finding Empty or Short Descriptions

To filter products with descriptions shorter than 10 characters:

SELECT product_name, description
FROM products
WHERE LEN(description) < 10;

This query returns products with brief descriptions, helping to identify records that might need more detailed text.

3. Using LEN() with a WHERE Clause to Check for Empty Strings

To find products with an empty description:

</>
Copy
SELECT product_name
FROM products
WHERE LEN(description) = 0;

This query returns products where description is an empty string, which can be useful for data quality checks.

4. Combining LEN() with CONCAT() for Custom Output

To display each product_name along with its character length in a formatted string:

</>
Copy
SELECT CONCAT(product_name, ' (', LEN(product_name), ' characters)') AS product_info
FROM products;

This query returns formatted output such as “Laptop (6 characters)”, combining the product name with its character count.

FAQs for SQL LEN()

1. What does the SQL LEN() function do?

The LEN() function returns the number of characters in a string, excluding trailing spaces in SQL Server.

2. How is LEN() different from DATALENGTH()?

LEN() returns the character count, while DATALENGTH() returns the byte size of data, which may differ in multi-byte character sets.

3. Does LEN() work with NULL values?

If the input is NULL, LEN() returns NULL.

4. Is LEN() supported in MySQL?

No, MySQL uses CHAR_LENGTH() instead of LEN() to get the number of characters in a string.

5. Can LEN() handle multi-byte characters?

Yes, LEN() counts characters rather than bytes, making it suitable for handling multi-byte characters.