PostgreSQL SUBSTRING Function

The SUBSTRING() function in PostgreSQL extracts a portion of a string based on specified position and length. It's essential for parsing text data, extracting codes, and manipulating string values.

Syntax

-- Standard SQL syntax
SUBSTRING(string FROM start [FOR length])

-- Alternative syntax
SUBSTRING(string, start [, length])

-- SUBSTR alias
SUBSTR(string, start [, length])

Official Documentation

Example usage

-- Extract substring from position 7 for 5 characters
SELECT SUBSTRING('Hello World' FROM 7 FOR 5) AS result;
-- Result: 'World'

-- Extract from position to end of string
SELECT SUBSTRING('PostgreSQL Database', 1, 10) AS result;
-- Result: 'PostgreSQL'

-- Extract area code from phone number
SELECT
    phone_number,
    SUBSTRING(phone_number, 1, 3) AS area_code,
    SUBSTRING(phone_number, 4, 3) AS exchange,
    SUBSTRING(phone_number, 7) AS line_number
FROM contacts;

-- Using negative positions (counts from end)
SELECT SUBSTRING('Document.pdf' FROM LENGTH('Document.pdf') - 2 FOR 3) AS extension;
-- Result: 'pdf'

-- Pattern matching with SUBSTRING
SELECT SUBSTRING('Invoice #12345' FROM '\d+') AS invoice_number;
-- Result: '12345' (extracts digits using regex)

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

Common issues

1. Position indexing: PostgreSQL uses 1-based indexing, not 0-based. The first character is at position 1.
2. Out of bounds: If start position is beyond string length, returns empty string (not an error).
3. Negative length: Not allowed and will cause an error.
4. NULL handling: Returns NULL if any argument is NULL.

Best practices

1. Use pattern matching syntax (FROM pattern) for regex-based extraction when working with complex patterns.
2. Combine with POSITION() or STRPOS() to find dynamic starting positions.
3. For extracting file extensions or domains, consider using dedicated functions or splits.
4. Use LEFT() or RIGHT() for simpler cases of extracting from start or end.

Frequently Asked Questions

Q: What's the difference between SUBSTRING and SUBSTR?
A: SUBSTR is an alias for SUBSTRING in PostgreSQL. They work identically. SUBSTRING is SQL standard, while SUBSTR is more common in other databases like Oracle.

Q: How do I extract text using a regular expression?
A: Use the pattern matching syntax: SUBSTRING('text' FROM 'pattern'). For example, SUBSTRING('Order #12345' FROM '\d+') extracts '12345'.

Q: Can I use negative positions to count from the end?
A: PostgreSQL doesn't support negative positions directly. Use SUBSTRING(string FROM LENGTH(string) - n + 1) or the RIGHT() function instead.

Q: What happens if I specify a length longer than the string?
A: SUBSTRING returns characters up to the end of the string without error. If start is beyond the string length, it returns an empty string.

Q: How do I extract everything after a specific character?
A: Combine with POSITION(): SUBSTRING(email FROM POSITION('@' IN email) + 1) to get everything after '@'. Alternatively, use SPLIT_PART() for delimiter-based splitting.