PostgreSQL: Find Last Occurrence of Character

I’ve spent more time than I’d like to admit trying to extract domain names from emails or file extensions from filenames. The challenge is that when you use POSITION() or STRPOS(), you get the first occurrence—exactly what you don’t want. You need the last occurrence to split a string properly. That’s where finding the last character becomes essential.

PostgreSQL doesn’t have a built-in “find last” function, but by combining REVERSE(), POSITION(), and some arithmetic, you can solve this elegantly. It’s a clever trick that works for any character or substring.

Quick Answer: Use Reverse with POSITION

To find the last occurrence:

SELECT
    email,
    POSITION('@' IN email) as first_at,
    LENGTH(email) - POSITION('@' IN REVERSE(email)) + 1 as last_at
FROM users
WHERE email IS NOT NULL
LIMIT 5;

The formula is: LENGTH(text) - POSITION(char IN REVERSE(text)) + 1. You reverse the string, find the position, then calculate back from the end.

How It Works

Here’s the logic: if you reverse a string, the “last” character in the original becomes the “first” in the reversed string. So finding the first occurrence in the reversed string tells you where the last was in the original.

Here’s a concrete example. For “hello@[email protected]”:

  1. Reverse it: “moc.elpmaxe@dlrow@olleh”
  2. Find @ in reversed: Position 8
  3. Convert back: LENGTH (20) - 8 + 1 = 13

Position 13 in the original string is where the last @ appears, which is correct.

The + 1 accounts for SQL’s 1-based indexing. The formula works because when you reverse a string of length N, position P in the reversed string corresponds to position (N - P + 1) in the original.

Different Approaches & When to Use Them

Approach 1: Reverse + POSITION (Most Common)

The straightforward approach using string reversal:

SELECT
    filename,
    LENGTH(filename) - POSITION('.' IN REVERSE(filename)) + 1 as extension_dot_pos,
    SUBSTRING(filename FROM LENGTH(filename) - POSITION('.' IN REVERSE(filename)) + 2) as file_extension
FROM files
WHERE filename LIKE '%.%';

This is what I use most often because it’s simple and readable. You’re finding the last period and everything after it. It works reliably for well-formed data.

Approach 2: Extract Everything After Last Character

Once you know the position of the last occurrence, extracting what comes after is straightforward:

SELECT
    email,
    SUBSTRING(email FROM LENGTH(email) - POSITION('@' IN REVERSE(email)) + 2) as domain,
    SUBSTRING(email FROM 1 FOR LENGTH(email) - POSITION('@' IN REVERSE(email))) as local_part
FROM users
WHERE '@' IN email;

Here you’re splitting the email into the local part (before @) and the domain (after @). This is a real use case—you often need to separate email components.

Approach 3: Using STRPOS (Alternative Syntax)

STRPOS() is similar to POSITION() but uses different syntax:

SELECT
    path,
    STRPOS(path, '/') as first_slash,
    CASE
        WHEN STRPOS(REVERSE(path), '/') > 0
        THEN LENGTH(path) - STRPOS(REVERSE(path), '/') + 1
        ELSE 0
    END as last_slash
FROM file_paths;

Both STRPOS() and POSITION() work the same way. STRPOS() returns 0 if not found (vs NULL for POSITION()), which can be useful for error handling.

Approach 4: Multiple Occurrences with Array Functions

If you need all occurrences, not just the last:

SELECT
    path,
    (STRING_TO_ARRAY(path, '/'))[array_length(STRING_TO_ARRAY(path, '/'), 1)] as last_part,
    (STRING_TO_ARRAY(path, '/'))[array_length(STRING_TO_ARRAY(path, '/'), 1) - 1] as second_to_last
FROM file_paths;

This splits on a delimiter and uses array indexing to get any part you want. It’s more flexible than position calculations.

Approach 5: Regex for Complex Patterns

For finding the last occurrence of more complex patterns:

SELECT
    text,
    -- Extract everything after the last digit
    SUBSTRING(text FROM '([0-9])([^0-9]*)$') as after_last_digit
FROM data
WHERE text ~ '[0-9]';

When you need sophisticated pattern matching, regex is more powerful than simple position calculations.

Real-World Example: Parse File Paths

Let me walk you through a practical scenario. You’re working with file paths and need to extract the filename, directory, and extension:

SELECT
    full_path,
    -- Find the last slash to get filename
    SUBSTRING(full_path FROM LENGTH(full_path) - POSITION('/' IN REVERSE(full_path)) + 2) as filename,
    -- Find directory path (everything before last slash)
    SUBSTRING(full_path FROM 1 FOR LENGTH(full_path) - POSITION('/' IN REVERSE(full_path))) as directory,
    -- Find the last period in filename for extension
    SUBSTRING(
        SUBSTRING(full_path FROM LENGTH(full_path) - POSITION('/' IN REVERSE(full_path)) + 2)
        FROM LENGTH(SUBSTRING(full_path FROM LENGTH(full_path) - POSITION('/' IN REVERSE(full_path)) + 2))
             - POSITION('.' IN REVERSE(SUBSTRING(full_path FROM LENGTH(full_path) - POSITION('/' IN REVERSE(full_path)) + 2))) + 1
    ) as extension
FROM file_uploads
WHERE full_path LIKE '%/%'
ORDER BY full_path;

This is complex but real. You’re parsing file paths—finding the directory, filename, and file extension. Breaking it down step by step with the reverse + position formula makes it manageable.

FAQ

Q: What happens if the character doesn’t exist? A: POSITION() returns 0 if not found, which would give a wrong result. Always check: WHERE '@' IN email or use CASE WHEN POSITION('@' IN text) > 0.

Q: Can I find the last of multiple different characters? A: Not directly. You’d use regex: REGEXP_REPLACE(text, '.*([pattern])', '\1') or split and process with arrays.

Q: How does this perform with large strings? A: Very efficiently. Reversing and finding position are fast native operations. Even with millions of rows, performance is fine.

Q: Can I find the second-to-last or third-to-last occurrence? A: The reverse method only finds the last. For multiple occurrences, use STRING_TO_ARRAY() with a delimiter or regex.

Q: What if there are multiple occurrences and I need all of them? A: Use STRING_TO_ARRAY(text, char) to split into an array, then work with array elements. Or use regex with global replacement.

Q: Is there a simpler way? A: Not in PostgreSQL’s built-in functions. The reverse + position formula is the standard approach. Some databases have LASTPOS() or similar, but PostgreSQL doesn’t.

Q: How do I handle NULL values? A: The formula returns NULL if the input is NULL. Always filter: WHERE text IS NOT NULL to avoid unexpected results.

Q: Can I nest this formula for finding occurrences at different positions? A: Yes, but it gets complex quickly. Consider using STRING_TO_ARRAY() or regex for multiple occurrence handling.

Q: Performance impact when using this in WHERE clauses? A: It’s fast for filtering, but won’t use indexes. If you filter frequently by “last character position”, consider storing it in a computed column.

Q: How does this work with Unicode or special characters? A: It works correctly. PostgreSQL handles Unicode internally. Just use the character you’re searching for: REVERSE(text) works with any character.

Common Errors & Solutions

Error: “Position calculation gives wrong result” Double-check your formula. It’s LENGTH(text) - POSITION(char IN REVERSE(text)) + 1. Missing the + 1 is a common mistake. Also verify the character actually exists in the string.

Error: “Getting position 0 or NULL” The character doesn’t exist in the string. Always use WHERE '@' IN email to filter. Or use CASE WHEN POSITION('@' IN text) > 0 THEN ... ELSE 0 END.

Error: “Substring extraction seems off by one” When extracting after the character, add 1 to the position: SUBSTRING(text FROM position + 1). When extracting before, subtract 1: SUBSTRING(text FROM 1 FOR position - 1).

Performance Tips

  • Very fast operation: String reversal and position finding are optimized native functions. Use freely.
  • In WHERE clauses: While it works, WHERE LENGTH(col) - POSITION(...) > value won’t use indexes. Filter on original columns when possible.
  • Pre-compute if searching repeatedly: If you frequently find the last occurrence in the same column, create a computed column or stored procedure.
  • With large strings: Reversing very long strings (>10MB) has overhead, but typical strings are fine.
  • Consider array splitting: For complex parsing (like file paths), STRING_TO_ARRAY() might be faster and more readable than nested position calculations.

Wrapping Up

Finding the last occurrence of a character in PostgreSQL requires the reverse + position trick: LENGTH(text) - POSITION(char IN REVERSE(text)) + 1. It’s not intuitive at first, but once you understand the logic—that reversing flips the problem—it becomes a reliable tool for extracting file extensions, domains from emails, or any last-occurrence need. Remember to always check that the character exists, and your formula will work reliably.