PostgreSQL LENGTH Function

PostgreSQL LENGTH Function

The LENGTH function in PostgreSQL is used to determine the number of characters or bytes in a string. It is commonly used for validations, data analysis, and formatting.

1. Basic Syntax

SELECT LENGTH(string);

• Returns the number of characters in the given string.
• Works with VARCHAR, TEXT, CHAR, and other string types.

Example: Basic Usage

SELECT LENGTH('Hello, World!');

✅ Output: 13 (counts spaces and punctuation)

2. Using LENGTH on Table Columns

You can use LENGTH to check the length of column values.

Example: Find Length of Names

SELECT first_name, LENGTH(first_name) AS name_length
FROM employees;

✅ Output:

first_name	name_length
John	4
Alexander	9

3. LENGTH vs. OCTET_LENGTH (Bytes vs. Characters)

Function	Measures	Example
LENGTH()	Character count	'Hello' → 5
OCTET_LENGTH()	Byte size	'Hello' → 5 (ASCII)

Example: Compare LENGTH and OCTET_LENGTH

SELECT LENGTH('Ñandú') AS char_count, OCTET_LENGTH('Ñandú') AS byte_size;

✅ Output (UTF-8 Encoding):

char_count	byte_size
5	6

🔹 Explanation:

• 'Ñandú' has 5 characters but 6 bytes due to the special character Ñ.

4. Find Records Where Length is Greater Than X

SELECT * FROM employees WHERE LENGTH(last_name) > 8;

✅ Finds employees with last names longer than 8 characters.

5. Using LENGTH with TRIM (Ignore Spaces)

SELECT LENGTH(TRIM('   PostgreSQL   '));

✅ Output: 10 (removes leading & trailing spaces before measuring length)

6. Example: Validate Input Length

SELECT first_name, LENGTH(first_name) AS name_length
FROM employees
WHERE LENGTH(first_name) < 3;

✅ Finds names with fewer than 3 characters (e.g., "Al", "Jo").

7. Summary

Use Case	Query	Output Example
Basic String Length	SELECT LENGTH('Hello');	5
Column Length	SELECT LENGTH(first_name) FROM employees;	Varies
Filter by Length	WHERE LENGTH(last_name) > 8	Names > 8 chars
Byte Size of String	SELECT OCTET_LENGTH('Ñandú');	6
Ignore Spaces	SELECT LENGTH(TRIM(' ABC '));	3

Would you like a real-world example for data validation? 🚀