Use SQL’s REPLACE function to find every occurrence of one text value inside another and substitute different text: SELECT REPLACE('blue car', 'blue', 'red'); returns red car. The function is available in many database systems, although details such as argument types, collation behavior, and how to handle patterns can vary by database.
What the SQL REPLACE function does
REPLACE is a string function: it takes an input string, a value to search for, and a replacement value. It returns a modified string; by itself, it does not change the stored data in a table. In common SQL dialects, it replaces every matching occurrence, not just the first one.
The basic form is:
REPLACE(string_expression, search_text, replacement_text)
For example, SELECT REPLACE('2026.11.03', '.', '-'); returns 2026-11-03. The first argument may be a literal, a column, or another expression that evaluates to text. The second argument is the text to look for, and the third is what to put in its place. Passing an empty replacement string removes matches, which is useful for deleting a known character or substring.
This operation is sometimes called literal string replacement. It is different from changing values with an UPDATE statement, and it is not automatically a regular-expression search. Those distinctions matter when deciding whether a query should display transformed text, update records, or match a more complicated pattern.
Basic examples with columns
To transform a column in query results, pass the column name to REPLACE. For example, suppose a table called products has a description column containing the phrase old model. This query displays descriptions with that phrase changed:
SELECT REPLACE(description, 'old model', 'previous model') AS revised_description FROM products;
The alias names the calculated output column; it does not rename or overwrite the original column. If the source description is old model - old model, the result is previous model - previous model in commonly used implementations.
Other practical examples include normalizing punctuation or removing characters that are known to be unwanted:
SELECT REPLACE(phone_number, '-', '') FROM contacts;removes hyphens from displayed phone numbers.SELECT REPLACE(file_name, ' ', '_') FROM uploads;changes spaces to underscores in the returned text.SELECT REPLACE(notes, 'TEMP:', '') FROM records;removes a specific label from returned notes.
These examples only transform the query result. If the application needs the original value for auditing, comparison, or later processing, retaining the original column and returning a separate transformed expression is often safer.
Replacing text in stored data
To save a replacement in a table, use REPLACE inside an UPDATE. For example, this statement changes matching text in every qualifying product description:
UPDATE products SET description = REPLACE(description, 'old model', 'previous model') WHERE description LIKE '%old model%';
The WHERE clause limits which rows are updated. Without it, the database may write a value for every row in the table, even when the text to replace is absent. Depending on the database and schema, that can create unnecessary work or trigger related rules and audit activity.
Before making a lasting change, inspect the affected rows with a SELECT that uses the same condition and replacement expression. Check how many rows qualify, and keep a recoverable backup or use a transaction where appropriate. The exact transaction commands and behavior depend on the database, but the principle is universal: a replacement expression can be harmless in a preview and consequential when used in an update.
Also consider whether the replacement could match text that should remain unchanged. Replacing cat with dog, for example, can alter the same sequence inside a larger word, such as catalog, unless the search condition or chosen technique accounts for word boundaries.
Differences across database systems
REPLACE is supported by major database products, including SQL Server, PostgreSQL, MySQL, SQLite, and Oracle, with familiar three-argument forms. The common behavior is to return a string with occurrences of the search text substituted. However, SQL is implemented by different database engines, so do not assume every edge case or type rule is identical.
- SQL Server: The function is written
REPLACE(string_expression, string_pattern, string_replacement). Its returned type follows the input expression’s character type; for non-large-value input, the result can be limited to 8,000 bytes. Use an appropriate large-value type, such asvarchar(max)ornvarchar(max), when a longer result is needed. - PostgreSQL: The text form is
replace(string, from, to). For regular-expression substitutions, PostgreSQL has the separateregexp_replacefunction. - MySQL and SQLite: Both provide a three-argument
replacefunction. Matching details can depend on the database’s text comparison rules or collations. - Oracle:
REPLACEaccepts the source string, search string, and replacement string; omitting the replacement argument removes matching text. Oracle also has its own rules for empty strings and character types.
When moving a query between systems, verify the target product’s documentation for return types, maximum lengths, collation behavior, and treatment of null or empty values. A query that works in one engine may need a cast or a different function in another.
Case sensitivity, collations, and matching
Whether uppercase and lowercase letters count as equal is not a universal property of REPLACE. It can depend on the database’s collation or comparison rules. A case-insensitive collation may treat Blue and blue as equivalent for matching, while a case-sensitive one may not. The database, column, or expression can have a collation, so behavior may differ even within one system.
Test with representative values before relying on case-sensitive behavior. If a replacement must apply only to a particular capitalization, use the database’s supported case-sensitive collation or a suitable expression, following that product’s syntax. Avoid assuming that wrapping text in LOWER solves every case: it changes the comparison form, and it does not by itself preserve or reconstruct the original capitalization.
REPLACE searches for literal text rather than interpreting special pattern symbols as regular expressions. For example, a period in the search string generally means a literal period, not “any character.” If the task involves alternatives, character classes, word boundaries, or variable patterns, look for the database’s regular-expression functions instead.
NULL values, empty strings, and data types
Null handling is an important source of surprises. In SQL Server, PostgreSQL, MySQL, and SQLite, a null argument generally makes the result null; a null source string cannot produce a normal replacement result. Oracle has special behavior because it treats a zero-length character string as null in many character contexts. Check the specific engine if empty strings and nulls have distinct meanings in the application.
An empty replacement is not the same as a null replacement. For example, REPLACE('a-b-c', '-', '') returns abc in common implementations, because the hyphens are replaced with no characters. A null third argument may instead produce null or follow database-specific rules. Do not substitute null when the intention is simply to delete the matched text.
Data type and length also matter. Some engines infer the result type from the source argument, while others impose particular limits or conversions. If text may contain Unicode characters, use the database’s appropriate Unicode-capable type and confirm that the connection and column preserve the required characters. A cast can help make intent explicit, but casting rules differ by product.
REPLACE compared with related functions
Choosing the right tool depends on what “replace” means in the task:
REPLACE: Substitutes literal occurrences of a substring. It is usually the clearest choice for changing a known fixed value wherever it occurs.- Regular-expression functions: Useful for pattern-based substitutions, such as replacing any run of digits or matching a word only at a boundary. Names and syntax differ; examples include PostgreSQL’s
regexp_replaceand regular-expression functions available in other engines. TRANSLATE: In systems that provide it, this is generally intended for character-by-character mapping, not replacing a multi-character phrase. Its availability and exact behavior vary.UPDATE: Changes stored rows when paired with an assignment expression such ascolumn = REPLACE(column, ...).REPLACEalone only returns a computed value.
A common misconception is that SQL REPLACE is equivalent to a programming-language replace function that changes only the first match. In many mainstream database implementations, it replaces all occurrences of the specified substring. If only the first match should change, use a product-specific function or expression and verify its documented behavior.
Performance and query design
Applying REPLACE to every row in a large result set requires the database to evaluate the expression for those rows. That is often reasonable for display or export, but repeatedly transforming a large text column can add work. In a filter such as WHERE REPLACE(code, '-', '') = 'AB12', the expression on the column may also prevent ordinary indexes on the unmodified column from being used efficiently. Optimizer behavior and available index options vary by engine.
When this kind of lookup is frequent, consider whether the data can be stored in a consistent normalized form, or whether the database supports an appropriate computed or expression index. Do not change stored data solely for speed without considering what the punctuation or formatting represents. For example, two phone-number strings that look equivalent after removing punctuation may still need country codes or extensions handled separately.
For one-time cleanup, a carefully scoped update may be appropriate. For ongoing transformations, it may be better to keep the original value and derive a normalized value in a view, generated column, or application layer, depending on the requirements. The best choice balances query cost, data integrity, and the need to reproduce the original input.
Safety and common mistakes
Using REPLACE in a query is not inherently dangerous, but broad updates and dynamically assembled SQL deserve care. Common problems include:
- Leaving off the
WHEREclause and updating more rows than intended. - Using a search phrase that is also part of longer words or identifiers.
- Assuming matching is case-sensitive without checking the collation.
- Ignoring null values, empty strings, Unicode, or output-length limits.
- Building SQL by concatenating untrusted user input into a statement.
If the search or replacement text comes from a user, use parameterized queries through the application’s database driver rather than inserting the value directly into SQL text. Parameters help keep data separate from SQL syntax, but placeholder notation depends on the driver and database. For destructive updates, preview the target rows, use a transaction when supported and suitable, and make sure a rollback or restore path is available.
Frequently Asked Questions
Does SQL REPLACE change the original table?
No. A SELECT REPLACE(...) computes text for the result set. To change stored values, use an UPDATE statement that assigns the result to a column.
Does REPLACE change every occurrence or only the first?
In common implementations such as SQL Server, PostgreSQL, MySQL, and SQLite, it substitutes every occurrence of the specified substring. Check the documentation for your database if exact first-match behavior matters.
How do I remove text with REPLACE?
Use an empty string as the third argument, for example REPLACE(description, 'draft:', ''). This removes each matching occurrence; the exact treatment of empty strings can differ, notably in Oracle.
Is SQL REPLACE case-sensitive?
It depends on the database’s collation and comparison behavior. Test values with different capitalization, and consult your database documentation if a particular case-sensitive or case-insensitive match is required.
Can REPLACE use wildcards or regular expressions?
Ordinary REPLACE searches for literal text, not a wildcard or regular-expression pattern. Use a database-specific regular-expression replacement function when the search needs pattern matching.
What happens if an argument is NULL?
Null arguments commonly result in a null output, but exact rules vary by engine. Oracle also treats empty character strings as null in many contexts, so check its rules when the distinction matters.
Related
- Split Feature in SOLIDWORKS: What It Does and How
- Split in SQL Server: STRING_SPLIT and Practical Examples
- SQL Split Syntax: Split Strings by Delimiter
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Convert Numbers to K in Excel: Formats and FormulasTo show numbers as thousands with a “k” in Excel, select the cells, press Ctrl+1, choose Number >…
- SEQUENCE in Excel: Build Number Series AutomaticallyIn Excel for Microsoft 365, use the SEQUENCE function to create a series of numbers that spills into…
- How to Copy a Formula Across an Entire Row in ExcelTo copy a formula across a row in Excel, enter it in the leftmost cell, select that cell…
- Excel Formula for Difference Between Two Numbers ExplainedTo find the difference between two numbers in Excel, subtract one cell from the other: if the first…
- Cash Flow Formula in Excel: Build and Read a SheetThe basic cash flow formula is cash received minus cash paid. In a sheet with cash inflows in…
- Microsoft Excel Date Today: TODAY Formula and Useful TipsTo show today’s date in Microsoft Excel for Microsoft 365 on Windows, enter =TODAY() in a cell and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.