Filtering and Transforming Data with SQL String Functions: LIKE, REGEXP, SUBSTRING, and REPLACE

Introduction

Imagine you've just inherited a customer database from a legacy system. The email addresses are inconsistently formatted, phone numbers are stored in five different styles, product codes are buried inside longer strings, and someone decided to abbreviate state names differently across a thousand rows. Your job is to clean this up and build a report — by end of day.

This is not a hypothetical. It happens constantly in data work. The good news is that SQL has a powerful set of string functions designed exactly for situations like this. With LIKE, REGEXP, SUBSTRING, and REPLACE, you can filter rows by pattern, extract meaningful pieces from messy text, and transform data in place — all without leaving your query. By the end of this lesson, you'll be able to handle real-world messy string data with confidence.

What you'll learn:

• How to use LIKE to filter rows based on partial text patterns
• How wildcard characters work and where they apply
• How REGEXP gives you far more expressive pattern matching than LIKE
• How to extract a specific portion of a string using SUBSTRING
• How to find and replace text within a string using REPLACE
• How to combine these functions to clean and reshape real-world data

---

Prerequisites

This lesson assumes you're comfortable writing basic SELECT statements with WHERE clauses, and that you understand what a string (text) column is. You don't need any prior experience with pattern matching or text manipulation. We'll build everything from the ground up.

The examples in this lesson use MySQL/MariaDB syntax. Where PostgreSQL or SQLite differ meaningfully, those differences are noted.

---

Understanding the Problem: Why String Functions Exist

Before diving into syntax, it's worth understanding why SQL needs special tools for text.

Numbers are easy for databases. Is 42 greater than 17? No ambiguity. Text is messier. "New York", "new york", "NEW YORK", "N.Y.", and "NY" all refer to the same place, but to a database they're completely different values. An exact WHERE city = 'New York' query would miss the other four.

String functions give you tools to match, extract, and modify text based on patterns and rules rather than exact equality. Think of them as a magnifying glass, a pair of scissors, and a correction pen — each doing a different job on the same messy document.

---

Filtering with LIKE: Pattern Matching for Humans

The Basic Idea

LIKE is the most approachable string filter in SQL. You use it in a WHERE clause to match rows where a column's value fits a certain pattern. Instead of requiring an exact match, it lets you use wildcard characters as stand-ins for unknown or variable parts of the text.

There are exactly two wildcards in LIKE:

• % — matches zero or more of any characters
• _ — matches exactly one of any character

Think of % as "anything goes here, including nothing" and _ as "there is exactly one character here, but I don't care which one."

Your First LIKE Query

Let's say you have a customers table with an email column, and you want to find all customers who use a Gmail address.

SELECT customer_id, full_name, email
FROM customers
WHERE email LIKE '%@gmail.com';

The % before @gmail.com tells SQL: "I don't care what comes before this — it could be john.smith or xyz123 or anything else — as long as the email ends with @gmail.com."

What gets matched:

• john.smith@gmail.com ✓
• xyz123@gmail.com ✓
• maria@gmail.com ✓

What doesn't:

• john@yahoo.com ✗
• support@gmail.co.uk ✗

Searching for Patterns in the Middle

You can place % at both ends to search for a pattern anywhere in the string.

SELECT product_id, product_name
FROM products
WHERE product_name LIKE '%bluetooth%';

This finds "Bluetooth Speaker", "Wireless Bluetooth Headset", "Bluetooth Adapter (USB)", and anything else containing the word "bluetooth" — but wait, there's a catch here we'll address in a moment.

Warning: LIKE is case-insensitive in MySQL by default, but case-sensitive in PostgreSQL. In MySQL, LIKE '%bluetooth%' will also match "Bluetooth" and "BLUETOOTH". In PostgreSQL, you'd use ILIKE for case-insensitive matching. Always test on your actual database to avoid surprises.

Using the Underscore Wildcard

The _ wildcard is useful when you know exactly how many characters a field should have. A common real-world use: product codes with a fixed format.

SELECT product_id, sku
FROM products
WHERE sku LIKE 'PRD-___-2024';

This matches SKUs like PRD-A1B-2024 or PRD-99X-2024 — any three-character segment in the middle — but not PRD-AB-2024 (only two characters) or PRD-ABCD-2024 (four characters).

What LIKE Can't Do

LIKE is excellent for simple patterns, but it has real limits:

• You can't specify a range of characters (e.g., "starts with any letter A through F")
• You can't say "repeat this pattern 3 times"
• You can't express "this character OR that character" in a single position

For those needs, you need REGEXP.

---

Pattern Matching with REGEXP: Precision Tools for Complex Patterns

What a Regular Expression Is

A regular expression (regex or regexp) is a mini-language for describing text patterns. Where LIKE gives you two wildcards, regex gives you a full vocabulary of operators for describing complex patterns precisely. The tradeoff is that regex takes more time to learn, but it pays back that investment many times over.

In SQL, you use it with the REGEXP keyword (MySQL) or ~ operator (PostgreSQL, where it becomes WHERE column ~ 'pattern').

The Essential Regex Vocabulary

Let's build up from simple to complex. Here are the most useful regex constructs:

Pattern	Meaning	Example Match
.	Any single character	c.t matches "cat", "cut", "c4t"
^	Start of string	^sales matches strings starting with "sales"
$	End of string	\.com$ matches strings ending with ".com"
[abc]	Any one of these characters	[aeiou] matches any vowel
[a-z]	Any character in this range	[a-zA-Z] matches any letter
[^abc]	Any character NOT in this set	[^0-9] matches any non-digit
*	Zero or more of the preceding	ab*c matches "ac", "abc", "abbc"
+	One or more of the preceding	ab+c matches "abc", "abbc" but not "ac"
{n}	Exactly n repetitions	[0-9]{3} matches exactly 3 digits
{n,m}	Between n and m repetitions	[0-9]{2,4} matches 2, 3, or 4 digits
\d	Any digit (0–9)	\d{4} matches a 4-digit year
\s	Any whitespace character

Practical REGEXP Examples

Find phone numbers in multiple formats:

You've got a contacts table where the phone column contains a mix of formats: (555) 867-5309, 555-867-5309, 5558675309. You want to find all valid 10-digit US phone numbers regardless of formatting.

SELECT contact_id, full_name, phone
FROM contacts
WHERE phone REGEXP '^[\(]?[0-9]{3}[\)\- ]?[0-9]{3}[\- ]?[0-9]{4}$';

Breaking this pattern down:

• ^ — start of string
• [\(]? — an optional opening parenthesis
• [0-9]{3} — exactly 3 digits (area code)
• [\)\- ]? — an optional closing paren, hyphen, or space
• [0-9]{3} — exactly 3 digits
• [\- ]? — an optional hyphen or space
• [0-9]{4} — exactly 4 digits
• $ — end of string

Find emails with suspicious domains:

SELECT user_id, email
FROM users
WHERE email REGEXP '@(tempmail|throwaway|mailinator)\.(com|net|org)$';

The | character means "OR" inside a regex. This catches emails ending in @tempmail.com, @throwaway.net, @mailinator.com, and similar combinations.

Find product codes that don't follow your expected format:

If your product codes should always be two uppercase letters followed by four digits (like AB1234), you can find the non-conforming ones:

SELECT product_id, sku
FROM products
WHERE sku NOT REGEXP '^[A-Z]{2}[0-9]{4}$';

NOT REGEXP inverts the match — you get all the rows that fail the pattern. This is a great way to audit data quality.

Tip: Regex patterns can get complex fast. Build them incrementally — start with a simple pattern, test it, then add more specificity. Trying to write the perfect regex in one shot is a reliable way to introduce subtle bugs.

---

Extracting Text with SUBSTRING: Cutting Out What You Need

The Concept

Sometimes you don't want to filter rows — you want to extract a specific portion of a string from every row. That's what SUBSTRING (also written as SUBSTR in many databases) does. You tell it where to start and how many characters to take, and it returns just that piece.

The syntax is:

SUBSTRING(column_name, start_position, length)

• start_position: Which character to start from. Position 1 is the first character (not 0 — this trips up programmers who are used to zero-indexed languages).
• length: How many characters to return. If you omit this, SQL returns everything from the start position to the end of the string.

A Simple Example

You have an orders table with an order_code column where every code looks like this: ORD-20240315-0042. The structure is:

• ORD- (prefix, positions 1–4)
• 20240315 (date in YYYYMMDD format, positions 5–12)
• - (separator, position 13)
• 0042 (sequence number, positions 14–17)

To extract just the date portion:

SELECT 
  order_code,
  SUBSTRING(order_code, 5, 8) AS order_date_raw
FROM orders;

Result:

order_code	order_date_raw
ORD-20240315-0042	20240315
ORD-20240316-0001	20240316

You can then format or cast that extracted string into a proper date type with additional functions.

Combining SUBSTRING with WHERE

You can also use SUBSTRING in a WHERE clause to filter based on part of a string:

SELECT order_id, order_code
FROM orders
WHERE SUBSTRING(order_code, 5, 4) = '2024';

This returns all orders where the year portion of the code is 2024. This is more precise than LIKE 'ORD-2024%' because it explicitly targets a known position.

SUBSTRING with Negative Positions (MySQL)

In MySQL, SUBSTRING also accepts a negative start position, which counts from the end of the string. This is useful when you know what's at the end of a string but not how long the beginning is.

SELECT 
  file_name,
  SUBSTRING(file_name, -4) AS file_extension
FROM uploaded_files;

This grabs the last 4 characters — perfect for extracting file extensions like .csv, .pdf, or .txt.

Note: Negative positions are a MySQL/MariaDB feature. In PostgreSQL, use RIGHT(column, n) to get the last n characters instead.

---

Transforming Text with REPLACE: Find-and-Fix at Scale

The Concept

REPLACE does exactly what it sounds like: it finds every occurrence of one substring within a string and replaces it with another. This is how you fix systematic problems across thousands of rows in a single query.

The syntax is:

REPLACE(column_name, 'find_this', 'replace_with_this')

An important detail: REPLACE is case-sensitive in most databases. REPLACE(column, 'hello', 'hi') won't affect a row containing "Hello".

Cleaning Up Phone Number Formatting

Let's say your contacts table has phone numbers stored with parentheses and hyphens — (555) 867-5309 — but your new system wants them as plain digits only: 5558675309. You can chain multiple REPLACE calls:

SELECT 
  contact_id,
  phone AS original_phone,
  REPLACE(REPLACE(REPLACE(REPLACE(phone, '(', ''), ')', ''), '-', ''), ' ', '') AS clean_phone
FROM contacts;

This works from the inside out:

1. Remove (
2. Remove )
3. Remove -
4. Remove spaces

The result is a clean 10-digit string. Because REPLACE returns a string, you can wrap it in another REPLACE — nesting as many as you need.

Updating Data in Place with REPLACE

The examples above use REPLACE in a SELECT query, which is great for previewing what the transformation would look like. When you're confident the results are right, use it in an UPDATE statement to actually change the data:

UPDATE contacts
SET phone = REPLACE(REPLACE(REPLACE(REPLACE(phone, '(', ''), ')', ''), '-', ''), ' ', '')
WHERE phone REGEXP '^[\(]?[0-9]{3}';

Notice we added a WHERE clause using REGEXP — a combination of two tools — to make sure we only update rows that look like phone numbers and not some other kind of data in that column.

Warning: Always run the SELECT version of your transformation first and review the results before running UPDATE. A UPDATE without a backup or transaction is very difficult to undo. Use BEGIN TRANSACTION or START TRANSACTION before your UPDATE if your database supports it, so you can ROLLBACK if something goes wrong.

Replacing Text to Standardize Values

Another common use: standardizing abbreviations. If your data has both "St." and "Street" in address lines:

UPDATE addresses
SET address_line1 = REPLACE(address_line1, ' St.', ' Street')
WHERE address_line1 LIKE '% St.%';

---

Putting It All Together: A Real Cleaning Scenario

Here's a query that combines all four functions to produce a cleaned report from a messy leads table:

SELECT
  lead_id,
  -- Extract the domain from email addresses
  SUBSTRING(email, LOCATE('@', email) + 1) AS email_domain,
  
  -- Clean phone numbers by stripping formatting characters
  REPLACE(REPLACE(REPLACE(phone, '-', ''), ' ', ''), '.', '') AS normalized_phone,
  
  -- Flag emails that look like disposable addresses
  CASE 
    WHEN email REGEXP '@(tempmail|mailinator|throwaway)\.' THEN 'Suspicious'
    ELSE 'OK'
  END AS email_quality,
  
  -- Extract year from a date-embedded lead_code like 'LD-2024-04521'
  SUBSTRING(lead_code, 4, 4) AS lead_year
  
FROM leads
WHERE 
  -- Only include leads with plausible email formats
  email LIKE '%@%.%'
  AND email NOT LIKE '%@%@%';

This single query is doing four distinct things at once: extracting, transforming, classifying, and filtering. This is the kind of query you'd write during data exploration or as part of a cleaning pipeline.

---

Hands-On Exercise

Work through these exercises using a sales_contacts table with the following columns: contact_id, full_name, email, phone, company_code.

Assume company_code values look like: ACME-US-2022-00142 (company abbreviation, country, year, sequence number).

Exercise 1: Write a query that returns all contacts whose email address ends in .edu. Use LIKE.

Exercise 2: Write a query that finds all contacts where the phone number contains characters other than digits, spaces, hyphens, and parentheses — these are likely malformed entries. Use REGEXP.

Exercise 3: Write a SELECT query that extracts the country code (positions 6–7 in the company_code) and the year (positions 9–12) as separate columns.

Exercise 4: Write a SELECT query that shows a cleaned version of the phone column where all hyphens and spaces have been removed.

Exercise 5 (Challenge): Combine Exercises 2 and 4: write a query that returns only the malformed phone numbers (from Exercise 2), alongside the cleaned version of each phone number (from Exercise 4), and the email domain extracted using SUBSTRING and LOCATE.

---

Common Mistakes & Troubleshooting

Mistake 1: Forgetting that LIKE needs wildcards to do anything interesting

WHERE email LIKE 'gmail.com' finds nothing, because it's looking for an email that is exactly "gmail.com". You need WHERE email LIKE '%gmail.com' or WHERE email LIKE '%@gmail.com'.

Mistake 2: Using REGEXP without anchors when you need exact matching

WHERE sku REGEXP '[0-9]{4}' will match any SKU that contains four consecutive digits — including ABCD12345XYZ. If you want SKUs that are only four digits, use WHERE sku REGEXP '^[0-9]{4}$'. The ^ and $ anchors are frequently forgotten and cause overly broad matches.

Mistake 3: Off-by-one errors in SUBSTRING

SQL string positions start at 1, not 0. SUBSTRING('Hello', 1, 3) returns 'Hel', not 'ell'. If you come from Python or JavaScript, this is a constant source of bugs. Always test with a known string before relying on the position.

Mistake 4: Running UPDATE with REPLACE without a WHERE clause

UPDATE table SET column = REPLACE(column, 'old', 'new') will update every single row. If your replacement logic has a bug, you've corrupted the entire table. Always scope your UPDATE with a WHERE clause, and always test with SELECT first.

Mistake 5: Expecting REPLACE to be case-insensitive

REPLACE(email, 'HTTP://', '') won't remove http:// or Http://. If you need case-insensitive replacement, you may need to handle multiple cases or use a combination of LOWER() with your logic.

Mistake 6: Deeply nesting REGEXP patterns without testing incrementally

Writing a 200-character regex in one shot and then debugging why it doesn't work is painful. Build it piece by piece: get the first part matching, verify, add the next piece, verify again.

---

Summary & Next Steps

You've now got a working toolkit for handling messy string data directly in SQL. Here's what you've covered:

• LIKE filters rows using % (any characters) and _ (one character) wildcards — great for simple, readable pattern matching
• REGEXP filters rows using the full power of regular expressions — precise, flexible, and essential for complex text patterns
• SUBSTRING extracts a specific portion of a string by position — perfect for pulling structured data out of composite fields
• REPLACE swaps out one piece of text for another — your primary tool for fixing systematic formatting problems

These functions shine brightest when combined. A typical data cleaning workflow might use REGEXP to identify malformed rows, SUBSTRING to extract meaningful parts, and REPLACE to normalize formatting — all in the same query or pipeline.

Where to go next:

• Explore TRIM, LTRIM, and RTRIM to handle leading and trailing whitespace — one of the most common data quality issues
• Learn CONCAT and CONCAT_WS to reassemble cleaned pieces into a single standardized string
• Investigate LOCATE (MySQL) or POSITION (standard SQL) to dynamically find where a character appears within a string — essential when fields don't have a fixed structure
• Look into stored procedures and views to encapsulate your cleaning logic so it can be reused across queries

String manipulation is one of the most practically valuable SQL skills you can develop. Real-world data is almost never perfectly formatted, and knowing how to handle it efficiently in-database — without exporting to Python or Excel — sets you apart as a data professional.