String length and position functions in PostgreSQL

Introduction

These functions help you find how long a text is and where a smaller text appears inside a bigger text.

When you want to count how many characters are in a word or sentence.

When you need to find the place of a specific word inside a sentence.

When checking if a certain part of text exists in a bigger text.

When splitting text based on where a word or character appears.

When cleaning or validating text data by length or position.

Syntax

PostgreSQL

LENGTH(string)
POSITION(substring IN string)

LENGTH returns the number of characters in the string.

POSITION returns the starting position of the substring inside the string, or 0 if not found.

Examples

Finds the number of characters in 'Hello World'.

PostgreSQL

SELECT LENGTH('Hello World');

Finds where the word 'World' starts in the text.

PostgreSQL

SELECT POSITION('World' IN 'Hello World');

Returns 0 because the string is empty.

PostgreSQL

SELECT LENGTH('');

Returns 0 because 'x' is not found in 'abc'.

PostgreSQL

SELECT POSITION('x' IN 'abc');

Sample Program

This query finds the length of the word 'Database' and the position where 'base' starts inside it.

PostgreSQL

SELECT LENGTH('Database') AS length_of_word,
       POSITION('base' IN 'Database') AS position_of_substring;

Important Notes

Positions start counting at 1, not 0.

If the substring is not found, POSITION returns 0.

LENGTH counts all characters including spaces and punctuation.

Summary

Use LENGTH to count characters in a string.

Use POSITION to find where a substring starts.

These functions help you understand and work with text data.