Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL LENGTH

The `LENGTH` function in PostgreSQL is a string function used to determine the number of characters in a string. It is useful for evaluating string length and managing text data efficiently in queries.

Usage

The `LENGTH` function is used when you need to calculate the character count of a string, which can be essential for data validation or formatting. It accepts a single string argument and returns an integer representing the number of characters.

LENGTH(string)

In this syntax, `string` is the input text whose length you want to determine.

Examples

1. Basic Length Calculation

SELECT LENGTH('Hello World');

This example returns `11`, as there are 11 characters, including the space, in the string "Hello World".

2. Length of a Column Value

SELECT LENGTH(first_name) 
FROM employees;

Here, the `LENGTH` function calculates the number of characters in each `first_name` entry from the `employees` table.

3. Using LENGTH in a Conditional Statement

SELECT first_name
FROM employees
WHERE LENGTH(first_name) > 5;

This query fetches `first_name` values from the `employees` table where the length exceeds 5 characters, useful for filtering data based on string length.

4. Handling Multibyte Characters

SELECT LENGTH('こんにちは'), OCTET_LENGTH('こんにちは');

This example demonstrates the difference between `LENGTH`, which returns the number of characters, and `OCTET_LENGTH`, which returns the number of bytes. For the multibyte string 'こんにちは', `LENGTH` returns `5`, while `OCTET_LENGTH` returns a larger byte count.

5. Using LENGTH with NULL Values

SELECT LENGTH(COALESCE(first_name, 'Unknown'))
FROM employees;

This uses `COALESCE` to handle potential `NULL` values in `first_name`, ensuring `LENGTH` always receives a valid string.

Tips and Best Practices

  • Consider character encoding. Be aware of multibyte character sets, as `LENGTH` counts characters, not bytes. Use `OCTET_LENGTH` to count bytes if needed.
  • Use for validation. Employ the `LENGTH` function to validate string input or ensure data meets specific formatting requirements.
  • Optimize with indexes. When filtering results based on string length, ensure appropriate indexes are in place to maintain performance. Note that using functions in `WHERE` clauses can affect index usage, so consider function-based indexes or restructuring queries for efficiency.
  • Combine with other string functions. Use `LENGTH` with other string functions like `TRIM` to refine input before length evaluation.
  • Error Handling. Handle `NULL` values by using functions like `COALESCE` to provide default values, ensuring robust query results.