How to Use REPLACE in SQL

Use REPLACE() when text values need one string fragment swapped for another inside query results. This is perfect for cleanup, formatting, standardization, and export-safe transformations.

What you’ll build or solve

You’ll learn how to use REPLACE in SQL to swap text inside strings. You’ll also know when to use it in SELECT output versus permanent updates.

When this approach works best

This approach is the right choice when repeated text fragments must be changed consistently.

Common real-world scenarios include:

• Removing spaces
• Rewriting domains
• Standardizing labels
• Cleaning imported SKUs
• Reformatting phone numbers

This is a bad idea when the replacement should affect only part of a structured format better handled with parsing logic.

Prerequisites

You only need:

• A SQL table with text columns
• Basic SELECT and UPDATE knowledge

Step-by-step instructions

Step 1: Pass the column, target text, and replacement

The common pattern is:

REPLACE(column, target, replacement)

Example:

SELECT REPLACE(phone_number, '-', '') AS clean_phone
FROM customers;

This removes all dashes.

You can replace one domain with another.

SELECT REPLACE(email, '@old.com', '@new.com') AS updated_email
FROM users;

This changes only the matching text fragment.

What to look for:

• Replaces every matching occurrence
• Great for cleanup and formatting
• Works inside SELECT and UPDATE
• Case sensitivity depends on database collation
• Use carefully for permanent updates

Examples you can copy

Remove spaces

SELECT REPLACE(full_name, ' ', '') AS compact_name
FROM users;

Fix domain

SELECT REPLACE(email, '@legacy.com', '@company.com')
FROM users;

SKU cleanup

SELECT REPLACE(sku, 'OLD-', 'NEW-')
FROM products;

Common mistakes and how to fix them

Mistake 1: Expecting only the first match to change

What the reader might do:

REPLACE(notes, 'error', 'warning')

Why it breaks: every matching occurrence is replaced.

Corrected approach:

Use parsing logic if only one position should change.

Mistake 2: Using it in UPDATE without previewing

What the reader might do:

UPDATE users
SET email = REPLACE(email, '@old.com', '@new.com');

Why it breaks: a broad pattern can rewrite too many rows.

Corrected approach:

Preview with SELECT first and use WHERE.

Mistake 3: Replacing ambiguous fragments

What the reader might do:

REPLACE(product_name, 'Pro', 'Basic')

Why it breaks: this may change unintended words.

Corrected approach:

Use more specific fragments.

Troubleshooting

If too much text changes, make the target fragment more specific.

If nothing changes, confirm exact casing and collation behavior.

If updating permanent data, preview with SELECT.

If only one occurrence should change, use parsing helpers instead.

Quick recap

• Use REPLACE() to swap text fragments
• Every matching occurrence changes
• Great for cleanup and standardization
• Preview before permanent updates
• Use WHERE in updates for safety