LENGTH()
SQL · String Functions
Theory
LENGTH(string) returns how many characters a string has. LENGTH('Asha') is 4. Every character counts, including spaces at the start or end, so LENGTH(' Ravi ') is 6. That makes LENGTH a quick way to detect hidden padding: compare LENGTH(name) with LENGTH(TRIM(name)) and any difference means stray spaces.
The name differs by dialect. SQL Server uses LEN(), which also ignores trailing spaces, and has DATALENGTH() for bytes. In MySQL, LENGTH() counts bytes, not characters, so text in Hindi or with emojis gives a bigger number than expected; use CHAR_LENGTH() there. Postgres, Oracle, SQLite and Snowflake LENGTH() count characters. LENGTH(NULL) returns NULL, not 0, and LENGTH('') is 0 (except in Oracle, where '' is NULL). Common uses are validation (a 10-digit mobile number must have LENGTH = 10) and filtering out empty values.
Why we use it
Lengths reveal data problems: hidden spaces, truncated codes, phone numbers with missing digits, empty strings that look like data. LENGTH lets you check and filter on these in plain SQL.
When to use it
Use LENGTH for validation rules, finding padded or empty values and with SUBSTRING arithmetic. In MySQL use CHAR_LENGTH for character counts; in SQL Server remember LEN() ignores trailing spaces.
How it works
- Take the input string
- Is it NULL? Yes: Return NULL. No: Start counting.
- Count each character, spaces included
- Counts bytes (MySQL)? Yes: Hindi/emoji count more than 1. No: Each character counts 1.
- Return the length
Syntax
LENGTH(string)Example
SELECT name, LENGTH(name) FROM employees;Key points
- Counts bytes in MySQL but characters in Postgres, Oracle, SQLite
- SQL Server spells it LEN() and ignores trailing spaces
- Spaces count, so LENGTH(x) <> LENGTH(TRIM(x)) finds padding
- LENGTH(NULL) is NULL, not 0
- Often used to validate input length, e.g. 10-digit phones