LENGTH()/LEN() in SQL

Returns the number of characters in a string. Note: Function name varies by DBMS (LENGTH in most, LEN in SQL Server).

Syntax

LENGTH(string)  -- Most databases
LEN(string)     -- SQL Server

Return type

INTEGER or similar numeric type

LENGTH()/LEN() Function Example

SELECT LENGTH('Hello World');
-- Output: 11

-- SQL Server version
SELECT LEN('Hello World');
-- Output: 11

What is LENGTH()/LEN() in SQL?

The LENGTH() (or LEN() in SQL Server) function in SQL returns the number of characters in a given string, including spaces. It is commonly used to validate text length, filter data based on string size, and optimize storage by ensuring text fields adhere to expected constraints. This function is supported across various SQL databases, with LENGTH() used in MySQL, PostgreSQL, and SQLite, while LEN() is specific to SQL Server. The function counts each character, including whitespace, and is frequently applied in text analysis, data validation, and string manipulation tasks.

Parameters:

string: The input string whose length is to be measured

Example Use Cases:

-- Validate password length

SELECT * FROM users WHERE LENGTH(password) < 8;
-- Output:
username    password      length   status
----------  ------------  -------  --------
john_doe    pass123      7        Too Short
mary_s      abcd12       6        Too Short
bob123      qwerty       6        Too Short
alice_w     pass12       6        Too Short
sam_user    short5       6        Too Short

-- Find longest product names

SELECT product_name, LENGTH(product_name) AS name_length 
FROM products 
ORDER BY name_length DESC;
-- Output:
product_name                           name_length
-------------------------------------  -----------
Professional Gaming Mechanical Keyboard    35
Wireless Noise-Cancelling Headphones      32
Ultra-Wide Curved Gaming Monitor          28
Bluetooth Portable Speaker System         29
High-Performance Gaming Mouse             26

-- Filter short descriptions

SELECT * FROM articles WHERE LENGTH(description) > 100;
-- Output:
title          description                                  length
-------------  -------------------------------------------  ------
SQL Basics     A comprehensive guide to SQL fundamentals... 120
Data Types     Understanding different SQL data types...    115
Joins Guide    Learn about SQL joins and their usage...    125
Indexes        Deep dive into database indexing...         110
Optimization   Tips for SQL query optimization...          105

Notes:

Behavior: Most DBMSs count all characters including spaces, but SQL Server's LEN() excludes trailing spaces, and Unicode character handling varies across DBMSs.
Performance Considerations: Very efficient, commonly used in indexes
Version Info: Core SQL function, available in all major versions