Oracle String REPLACE Function: Syntax and Examples

Formulas & functions · Updated November 22, 2026

Oracle’s string replacement function is REPLACE: use REPLACE(source, search_text, replacement_text) to replace every occurrence of one literal substring with another. For example, REPLACE('red-blue-red', 'red', 'green') returns green-blue-green; omit the replacement argument, or pass it as NULL, to remove the matched text.

What Oracle REPLACE Does

REPLACE is a built-in SQL character function in Oracle Database. It searches a source value for a specified substring and substitutes the replacement value wherever that substring occurs. It is useful for straightforward text changes such as standardizing labels, removing punctuation, correcting a known spelling, or cleaning a field before displaying or comparing it.

The key point is that the function replaces all matching occurrences, not just the first one. If a value contains the search text several times, each match is changed in the returned value. The original value is not modified just because the function appears in a query: the function produces a result, and a separate data-changing statement is needed to store that result back in a table.

REPLACE looks for literal text, not a pattern. Searching for %, for example, looks for a percent sign; it does not act as a wildcard. This distinction matters because SQL wildcards belong to pattern-matching operations such as LIKE, while regular-expression patterns require REGEXP_REPLACE.

Syntax and Argument Meaning

The general Oracle SQL syntax is REPLACE(char, search_string [, replacement_string]). The brackets in the reference syntax mean that the third argument is optional; they are not typed as part of a SQL expression.

  • char is the source character value to examine.
  • search_string is the literal sequence of characters to find.
  • replacement_string is the text to put in place of each match. If it is omitted or evaluates to NULL, the matching text is removed.

Here is a complete query that can be run in Oracle SQL:

SELECT REPLACE('JACK and JUE', 'J', 'BL') AS changed_text FROM dual;

The result is BLACK and BLUE. Oracle’s DUAL table is a one-row table commonly used to evaluate an expression without selecting from an application table. In normal queries, replace it with the table and column containing the text you want to process.

Practical Examples

These examples show the main patterns. Each expression changes only the returned text; a query using it does not update stored data.

  • Change every hyphen to a space: REPLACE('north-east-west', '-', ' ') returns north east west.
  • Remove a fixed prefix wherever it appears: REPLACE('ID-42-ID-87', 'ID-', '') returns 42-87.
  • Remove every period: REPLACE('1.234.567', '.') returns 1234567.
  • Replace a word in a column: SELECT REPLACE(description, 'colour', 'color') FROM products; returns a transformed value for each selected row.

The last example does not distinguish a whole word from the same letters embedded in a longer word. For instance, replacing cat can also change the sequence inside catalog. If matches need word boundaries or more complicated rules, a regular-expression function may be a better fit.

To save transformed values, an UPDATE can use the function, such as UPDATE products SET description = REPLACE(description, 'colour', 'color');. This changes table data, unlike a SELECT. Before running a broad update, check which rows will be affected with a corresponding query, consider a transaction, and follow the database’s normal backup and change-control practices.

NULLs, Empty Strings, and Missing Search Text

Oracle’s handling of nulls is important when using REPLACE. If the source value is NULL, the result is NULL. Oracle also treats a zero-length character string written as '' as NULL in SQL. Consequently, an empty replacement string removes the matching text, just as an explicit null replacement does.

If search_string is null, REPLACE returns the source value unchanged. This often surprises people who expect a null search argument to remove or replace something. It does not identify a match. You can test the actual result with expressions such as SELECT REPLACE('abc', NULL, 'x') FROM dual;; the value remains abc.

These rules can affect columns that are nullable or produced by expressions. A null replacement expression means deletion of matches, whereas a null search expression means no replacement. If a query should use a fallback value when a column is null, apply an appropriate null-handling expression such as NVL to the relevant argument, after deciding what fallback makes sense for the data.

Matching, Case, and Replacement Order

In ordinary Oracle configurations, REPLACE matches character sequences with case distinctions: searching for abc does not normally match ABC. Oracle collation settings and the collation assigned to character expressions can affect comparison behavior, so applications with non-default linguistic collations should verify the behavior for their database and expression. Do not assume that changing a session’s display format changes text matching.

The match is a substring match, not a whole-token match. It also does not accept a start position or an occurrence number: there is no built-in argument meaning replace only the third match or begin looking at character five. If you need that control, use an alternative described below.

For overlapping-looking text, replacement proceeds through matches in the source rather than repeatedly applying the function to newly inserted replacement text. A useful practice is to test ambiguous inputs directly, especially when the search text can occur within itself or when the replacement contains the search text. Nested calls have a separate ordering issue: REPLACE(REPLACE(value, 'a', 'b'), 'b', 'c') first changes a to b, then changes all b characters—including the newly created ones—to c. The order of nested operations therefore matters.

REPLACE Compared with REGEXP_REPLACE

Use REPLACE when the search value is fixed literal text. Its compact syntax is easy to read, and it avoids the extra pattern rules that come with regular expressions. Use REGEXP_REPLACE when the matching rule is a pattern, when you need to choose an occurrence or starting position, or when you need features such as character classes and capture groups.

For example, to replace runs of one or more digits with a hash sign, a regular expression is appropriate: REGEXP_REPLACE('order 123, batch 45', '[0-9]+', '#') returns order #, batch #. The pattern [0-9]+ means one or more digits; it is not interpreted as a literal sequence. By contrast, REPLACE('order 123', '[0-9]+', '#') searches for the exact characters [0-9]+ and will not replace the digits.

REGEXP_REPLACE offers more flexibility but is not automatically the better choice for simple substitutions. A literal search is clearer with REPLACE, and a regular expression can be harder to maintain or can require more processing. Choose the simplest function that expresses the required rule.

REPLACE Compared with TRANSLATE

TRANSLATE is another Oracle string function, but it maps individual characters rather than replacing a multi-character substring as a unit. For example, TRANSLATE('123-456', '123', 'abc') maps 1 to a, 2 to b, and 3 to c, producing abc-456. It is handy when many single-character substitutions are needed.

Use REPLACE for a word, a phrase, or any other multi-character search value. Use TRANSLATE for character-by-character conversion, such as mapping a set of digits or punctuation characters. If the character mapping string is shorter than the set of characters to translate, characters without a corresponding replacement are removed; confirm that this behavior is intended before using it to clean data.

Common Problems and Good Practice

Most mistakes come from expecting REPLACE to do more than literal, all-occurrence substitution. Check these points when a result looks unexpected:

  • Confirm the search text exactly, including spaces, punctuation, and capitalization.
  • Check whether the search value or replacement expression evaluates to NULL.
  • Remember that the search can match inside a larger word or identifier.
  • Use REGEXP_REPLACE for patterns, positions, or occurrence-specific replacement rather than trying to use wildcard characters with REPLACE.
  • Test nested calls in the intended order, since one call can create text that a later call then matches.
  • Preview an UPDATE with a SELECT and a suitable WHERE clause before changing production data.

For large tables, consider how the expression is used in filters. A condition such as WHERE REPLACE(column_name, '-', '') = '12345' applies a function to the column, which may prevent Oracle from using a conventional index on the unmodified column for that comparison. Whether an index can help depends on the query and database design; if this lookup is frequent, a function-based index or a stored normalized value may be worth discussing with the database administrator. Do not add one without checking write overhead, storage, and the actual execution plan.

Frequently Asked Questions

Does Oracle REPLACE change every occurrence?
Yes. It replaces every occurrence of the specified literal search string in the source value; it has no argument for selecting only the first match.

How do I remove a substring in Oracle?
Use REPLACE(source_value, search_text), or provide a null replacement argument. For example, REPLACE('A-B-C', '-') returns ABC.

Is Oracle REPLACE case-sensitive?
With ordinary default comparison behavior, case matters. Collation settings can affect comparison rules, so check the collation of the expression and database if your application uses linguistic or case-insensitive collations.

Does REPLACE support wildcards or regular expressions?
No. Its search argument is literal text. Use REGEXP_REPLACE when the search must be a regular-expression pattern.

How can I replace only one occurrence?
REPLACE cannot select an occurrence. Use REGEXP_REPLACE with its occurrence argument when its pattern-based behavior fits the requirement, or use more targeted string logic for a literal position-specific change.

Does REPLACE update a table column?
No. In a SELECT, it only returns a transformed value. To store the result, use an UPDATE statement, and review the affected rows before committing the change.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.