Advanced String Functions: REPLACE, SPLIT, and REGEXP
You'll learn how to surgically modify text, break strings into pieces, and match complex patterns using SQL's most powerful string tools.
Beyond basic trimming and concatenation, SQL offers sophisticated functions for real-world text wrangling—like cleaning messy data, parsing structured strings, or finding patterns that aren't simple wildcards.
REPLACE: Swapping Text
REPLACE finds all occurrences of a substring and swaps them for something new. Think of it as "find and replace" from a word processor:
SELECT REPLACE(email, '@oldcompany.com', '@newcompany.com') AS updated_email
FROM users;
This updates email domains without touching the username part—perfect for bulk data corrections.
SPLIT: Breaking Strings Apart
SPLIT (or STRING_TO_ARRAY in some databases) converts delimited text into arrays. Imagine taking "apples,oranges,bananas" and turning it into separate, indexable pieces:
SELECT SPLIT_PART(full_name, ' ', 1) AS first_name,
SPLIT_PART(full_name, ' ', 2) AS last_name
FROM customers;
This extracts first and last names from "John Doe"—essential when dealing with CSV-like data stored in columns.
REGEXP: Pattern Matching with Superpowers
Regular expressions (REGEXP_REPLACE, REGEXP_MATCHES) let you match complex patterns—not just exact text. Want to extract area codes from phone numbers, validate email formats, or strip all non-numeric characters?
SELECT REGEXP_REPLACE(phone, '[^0-9]', '', 'g') AS clean_phone
FROM contacts;
This removes everything except digits from "(555) 123-4567" → "5551234567".
Key Takeaway:
REPLACE swaps fixed text, SPLIT breaks strings by delimiters into parts, and REGEXP handles complex pattern matching—giving you precision control over messy, real-world text data.