PostgreSQL REPLACE Function

The REPLACE() function in PostgreSQL searches for all occurrences of a substring within a string and replaces them with a different substring. It's essential for data cleaning, text transformation, and string manipulation tasks.

Syntax

REPLACE(string, from_substring, to_substring)

Official Documentation

Example usage

-- Basic replacement
SELECT REPLACE('Hello World', 'World', 'PostgreSQL') AS result;
-- Result: 'Hello PostgreSQL'

-- Remove characters (replace with empty string)
SELECT REPLACE('(555) 123-4567', '-', '') AS result;
-- Result: '(555) 1234567'

-- Clean phone numbers
UPDATE contacts
SET phone = REPLACE(REPLACE(REPLACE(phone, '-', ''), '(', ''), ')', '')
WHERE phone LIKE '%-%' OR phone LIKE '%(%';

-- Replace multiple spaces with single space
SELECT REPLACE('Hello    World', '    ', ' ') AS result;
-- Result: 'Hello World'

-- Case-sensitive replacement
SELECT REPLACE('Apple, apple, APPLE', 'apple', 'orange') AS result;
-- Result: 'Apple, orange, APPLE' (only lowercase 'apple' replaced)

-- Sanitize file paths
SELECT REPLACE(file_path, '\', '/') AS normalized_path
FROM documents;

-- Replace in JSON strings
SELECT REPLACE(metadata::text, '"active":false', '"active":true') AS updated
FROM settings;

Common issues

1. Case sensitivity: REPLACE is case-sensitive. 'Hello' and 'hello' are different.
2. All occurrences: REPLACE replaces all occurrences, not just the first one.
3. No pattern matching: REPLACE works with literal strings only, not regex patterns.
4. NULL handling: Returns NULL if any argument is NULL.

Best practices

1. For case-insensitive replacement, combine with LOWER() or use REGEXP_REPLACE().
2. Chain multiple REPLACE calls for complex transformations, but consider REGEXP_REPLACE for better performance.
3. Use REPLACE to sanitize user input by removing or replacing unwanted characters.
4. For pattern-based replacements, use REGEXP_REPLACE() instead.

Frequently Asked Questions

Q: How do I make REPLACE case-insensitive?
A: PostgreSQL's REPLACE is case-sensitive. For case-insensitive replacement, use REGEXP_REPLACE with the 'i' flag: REGEXP_REPLACE(string, 'pattern', 'replacement', 'gi').

Q: Can REPLACE handle regular expressions?
A: No, REPLACE only works with literal string matching. Use REGEXP_REPLACE() for pattern-based replacements.

Q: How do I replace only the first occurrence?
A: REPLACE replaces all occurrences. To replace only the first, use: OVERLAY(string PLACING 'new' FROM POSITION('old' IN string) FOR LENGTH('old')) or REGEXP_REPLACE with a limit.

Q: What happens if the search string isn't found?
A: REPLACE returns the original string unchanged if the search substring is not found. No error is raised.

Q: How do I replace multiple different substrings at once?
A: Chain multiple REPLACE calls: REPLACE(REPLACE(REPLACE(str, 'a', '1'), 'b', '2'), 'c', '3'). For complex patterns, use REGEXP_REPLACE or TRANSLATE function.