PostgreSQL POSITION Function

The POSITION() function in PostgreSQL returns the position of a substring within a string. It's useful for locating text, parsing data, and extracting information based on delimiter positions.

Syntax

-- SQL standard syntax
POSITION(substring IN string)

-- Alternative syntax (PostgreSQL specific)
STRPOS(string, substring)

Official Documentation

Example usage

-- Find position of substring
SELECT POSITION('World' IN 'Hello World') AS position;
-- Result: 7

-- Using STRPOS (equivalent)
SELECT STRPOS('Hello World', 'World') AS position;
-- Result: 7

-- Check if substring exists
SELECT email
FROM users
WHERE POSITION('@' IN email) > 0;

-- Extract domain from email
SELECT
    email,
    SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM users;

-- Find first space in a name
SELECT
    full_name,
    SUBSTRING(full_name, 1, POSITION(' ' IN full_name) - 1) AS first_name
FROM contacts
WHERE POSITION(' ' IN full_name) > 0;

-- Case-sensitive search
SELECT POSITION('world' IN 'Hello World') AS position;
-- Result: 0 (not found, case-sensitive)

-- Split string at delimiter
SELECT
    url,
    SUBSTRING(url, 1, POSITION('://' IN url) - 1) AS protocol,
    SUBSTRING(url FROM POSITION('://' IN url) + 3) AS rest
FROM websites;

Common issues

1. Case sensitivity: POSITION is case-sensitive. 'Hello' and 'hello' are different.
2. Not found returns 0: When substring is not found, returns 0 (not -1 or NULL).
3. Finds first occurrence only: Returns position of first match, not all matches.
4. 1-based indexing: PostgreSQL uses 1-based indexing (first position is 1, not 0).

Best practices

1. Use POSITION > 0 to check if a substring exists before using it for extraction.
2. For case-insensitive search, convert both strings to the same case: POSITION(LOWER('search') IN LOWER(column)).
3. Combine with SUBSTRING for powerful text extraction and parsing.
4. For finding last occurrence, use REGEXP-based functions or reverse the string logic.

Frequently Asked Questions

Q: What's the difference between POSITION and STRPOS?
A: They're functionally equivalent but have different syntax. POSITION uses SQL standard syntax POSITION(substring IN string), while STRPOS uses STRPOS(string, substring). STRPOS is PostgreSQL-specific but more concise.

Q: How do I make POSITION case-insensitive?
A: Convert both strings to the same case: POSITION(LOWER('search') IN LOWER(column)). Alternatively, use ILIKE with pattern matching for existence checks.

Q: What does POSITION return if the substring is not found?
A: It returns 0 (zero), not -1 or NULL. Always check for > 0 to verify if the substring exists.

Q: How do I find the last occurrence of a substring?
A: PostgreSQL doesn't have a built-in RPOSITION function. Use: LENGTH(string) - POSITION(REVERSE(substring) IN REVERSE(string)) + 1 or use REGEXP_MATCHES.

Q: Can POSITION find multiple occurrences?
A: No, POSITION only returns the first occurrence. For multiple matches, use REGEXP_MATCHES() or write a custom function with loops.