SQL LENGTH Function: Get String Length (All Databases)

Overview

Get the number of characters in a string. The function name differs across databases, and some count bytes while others count characters.

MySQL

SELECT LENGTH('Hello');       -- 5 (bytes)
SELECT CHAR_LENGTH('Hello');  -- 5 (characters)

-- For multibyte (UTF-8) strings:
SELECT LENGTH('日本語');       -- 9 (3 bytes per char)
SELECT CHAR_LENGTH('日本語');  -- 3 (characters)

-- Find users with short names:
SELECT * FROM users WHERE CHAR_LENGTH(name) < 3;

-- Validate data:
SELECT * FROM users WHERE CHAR_LENGTH(phone) NOT BETWEEN 10 AND 15;

PostgreSQL

SELECT LENGTH('Hello');       -- 5 (characters)
SELECT CHAR_LENGTH('Hello');  -- 5 (characters)
SELECT OCTET_LENGTH('Hello'); -- 5 (bytes)

-- PostgreSQL LENGTH counts characters, not bytes:
SELECT LENGTH('日本語');  -- 3

-- Pad strings to fixed length:
SELECT LPAD(code, 5, '0') FROM products;  -- '00042'

SQL Server

SELECT LEN('Hello');       -- 5
SELECT DATALENGTH('Hello');  -- 5 (bytes)

-- LEN trims trailing spaces:
SELECT LEN('Hello   ');       -- 5 (trimmed!)
SELECT DATALENGTH('Hello   '); -- 8 (includes spaces)

-- For Unicode:
SELECT LEN(N'日本語');        -- 3
SELECT DATALENGTH(N'日本語'); -- 6 (2 bytes per nchar)

Oracle

SELECT LENGTH('Hello') FROM DUAL;  -- 5 (characters)
SELECT LENGTHB('Hello') FROM DUAL; -- 5 (bytes)

-- Oracle LENGTH returns NULL for empty strings:
SELECT LENGTH('') FROM DUAL;  -- NULL (not 0!)
-- Use NVL to handle:
SELECT NVL(LENGTH(name), 0) FROM users;

Skip the Syntax Lookup

Instead of memorizing LENGTH / LEN syntax for each database, describe what you need in plain English and let AI2SQL generate the correct query.

Frequently Asked Questions