SQL REPLACE Function: Syntax, Usage, and Examples

The SQL REPLACE function lets you substitute part of a string with another value. It comes in handy when you need to clean, format, or transform text data stored in your tables.

How to Use the SQL REPLACE Function

The REPLACE function works with the following syntax:

REPLACE(original_string, substring_to_replace, replacement_string)

• original_string: the string you're working with.
• substring_to_replace: the portion of the string you want to change.
• replacement_string: the new text you want in place of the old one.

The function returns a new string with the specified substitution applied. If the substring isn’t found, the original string remains unchanged.

You can use the REPLACE function in SELECT queries, UPDATE statements, and anywhere else SQL expressions are allowed.

When to Use the SQL REPLACE Function

You’ll reach for the SQL REPLACE function when you want to correct, clean, or restructure string values. It’s especially useful in these scenarios:

1. Cleaning Up Input Data

If your database has user-submitted entries, you might find unwanted characters like extra spaces, outdated company names, or inconsistent punctuation. You can apply the REPLACE function to standardize these entries without manually editing each row.

2. Formatting Text for Display

Need to swap out a dash for an em-dash? Or change “&” to “and” in product titles? Use REPLACE to prep strings for display in a consistent, readable format.

3. Fixing Schema Changes

Say a product catalog used the abbreviation “Pkg” for years, but now your team wants the full “Package.” The REPLACE function can quickly transform thousands of rows with a single query.

Examples of the SQL REPLACE Function

Let’s look at a few real-world examples to see how the REPLACE function in SQL works in practice.

Example 1: Replace a Character in a Column

SELECT REPLACE('hello-world', '-', ' ') AS modified_string;

This replaces the hyphen with a space, returning:

hello world

This is helpful when converting slugs or URL-friendly strings back to normal phrases.

Example 2: Clean Up Typos

SELECT REPLACE('Thank yu for your purchase', 'yu', 'you') AS fixed;

The result is:

Thank you for your purchase

Perfect when fixing repeated mistakes in customer messages or marketing templates.

Example 3: Replace Substrings in a Table Column

SELECT REPLACE(product_name, 'USB-C', 'USB Type-C') AS updated_name
FROM products;

This query changes every mention of “USB-C” to “USB Type-C” in the product_name column. You don’t modify the data yet—you’re just displaying the changed version.

Example 4: Use in an UPDATE Query

If you’re ready to save those changes to the database, combine REPLACE with UPDATE:

UPDATE products
SET product_name = REPLACE(product_name, 'USB-C', 'USB Type-C')
WHERE product_name LIKE '%USB-C%';

This permanently updates only rows that contain the string “USB-C.” The LIKE operator in the WHERE clause helps avoid unnecessary writes to rows that don’t need changing.

Example 5: Remove Extra Whitespace

SELECT REPLACE('Too     many   spaces', '  ', ' ') AS cleaned;

You can repeat this process to reduce multiple spaces to single ones. Use nested REPLACE calls if needed:

SELECT REPLACE(REPLACE('Too     many   spaces', '  ', ' '), '  ', ' ') AS cleaned;

While it’s a bit of a hack, it gets the job done when trimming isn’t enough.

Learn More About the SQL REPLACE Function

Replace Function in SQL vs. Other String Functions

REPLACE is great for substitutions, but not for everything. It doesn’t support regular expressions, so you can’t use patterns like [0-9]+ to find numbers. For more advanced replacements, some databases offer REGEXP_REPLACE.

Also, if you only want to extract part of a string without replacing anything, use SUBSTRING() or LEFT() and RIGHT() instead. If you're wondering how REPLACE compares to UPDATE, think of it this way: REPLACE works inside an expression, while UPDATE changes entire values. Together, they make a powerful combo for text transformations.

Using the SQL REPLACE Function in Different Dialects

The REPLACE function is available in most relational database systems, including:

• MySQL
• PostgreSQL
• Microsoft SQL Server
• SQLite
• Oracle

The syntax remains largely the same across systems. However, in PostgreSQL, REPLACE is case-sensitive. That means 'abc' and 'ABC' are treated differently. If you need a case-insensitive version, wrap your string in LOWER() or UPPER():

SELECT REPLACE(LOWER(message), 'error', 'issue') FROM logs;

Or use ILIKE when filtering with WHERE:

SELECT * FROM logs
WHERE message ILIKE '%error%';

Just be aware that REPLACE will still respect casing when substituting. It won’t lowercase the result unless you explicitly apply a string function.

Combining SQL REPLACE with Other Functions

You can pair REPLACE with many other SQL functions for more precise control.

Trim and Replace

SELECT REPLACE(TRIM(user_input), '  ', ' ') FROM feedback;

This trims leading/trailing whitespace before cleaning up the middle.

Nested REPLACE for Multiple Changes

SELECT REPLACE(REPLACE(title, '&', 'and'), '-', ' ') AS cleaned_title
FROM blog_posts;

You don’t need a separate query for each cleanup step. Nest them in a single expression to tidy up multiple formatting issues at once.

Replace Null Values with COALESCE

SELECT REPLACE(COALESCE(notes, ''), 'N/A', '') FROM orders;

This ensures you don’t try to run REPLACE on a NULL value, which would return NULL. Always use COALESCE() or check for nulls when applying string functions to optional fields.

Performance of the SQL REPLACE Function

SQL REPLACE is relatively lightweight, but if you’re using it in queries on large tables, it’s a good idea to test performance. Try adding an index to the column you’re filtering with WHERE or LIKE. Keep in mind, though, that if you're replacing inside an indexed column, the new value might invalidate the index, triggering a full table scan. In that case, consider storing the updated values in a new column or applying changes during an ETL process.

Replace Function in SQL Query Templates

SQL query templates often use the REPLACE function to fill in placeholders in stored messages or templates. For example, if your system uses a message like "Dear [Name], your order has shipped," you could replace [Name] dynamically:

SELECT REPLACE('Dear [Name], your order has shipped.', '[Name]', customer_name)
FROM orders;

This adds a personal touch without hardcoding dozens of messages.

Replace Function for JSON Fields

In databases that store JSON in text format, REPLACE can also help update values without parsing full JSON structures. While this approach is limited and not ideal for full JSON manipulation, it can work for quick fixes:

SELECT REPLACE(json_column, '"status":"pending"', '"status":"complete"') AS updated_json
FROM api_responses;

If your platform supports JSON functions, use those instead. But if you’re in a pinch, this technique can help clean or migrate structured text data fast.

Replace Function in SQL Query Pipelines

When building pipelines for transforming incoming data, you might use REPLACE to:

• Remove escape characters from API responses
• Format currency symbols
• Change locale-specific formats (like commas vs. periods in numbers)
• Update deprecated terminology across logs or datasets

Using the REPLACE function in SQL query pipelines lets you avoid adding extra logic to your backend code. It moves the cleanup closer to the data, which often improves performance and maintainability.

Replace vs. Translate in SQL

Some SQL engines support the TRANSLATE() function, which swaps individual characters instead of full strings:

SELECT TRANSLATE('abc123', 'abc', 'xyz');
-- Output: xyz123

This is useful when you need character-level substitution, like changing vowels or replacing punctuation. For replacing entire words or phrases, REPLACE is the better fit.

Working with the SQL REPLACE function helps you streamline text handling, fix mistakes, and prepare your data for display or reporting. It’s one of those small, often-overlooked functions that save you time in big ways.