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.
charis the source character value to examine.search_stringis the literal sequence of characters to find.replacement_stringis the text to put in place of each match. If it is omitted or evaluates toNULL, 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', '-', ' ')returnsnorth east west. - Remove a fixed prefix wherever it appears:
REPLACE('ID-42-ID-87', 'ID-', '')returns42-87. - Remove every period:
REPLACE('1.234.567', '.')returns1234567. - 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_REPLACEfor patterns, positions, or occurrence-specific replacement rather than trying to use wildcard characters withREPLACE. - Test nested calls in the intended order, since one call can create text that a later call then matches.
- Preview an
UPDATEwith aSELECTand a suitableWHEREclause 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
- Python String Reverse: Methods and Examples
- Split String in JavaScript: Methods, Limits, and Examples
- How to Split in Onshape: Parts, Faces, and Sketches
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNTIF Not Equal to Text: Formula & FixesTo count cells in Excel that do not equal a specific text value, use the formula =COUNTIF(range,"text"). For…
- Find and Replace Formula in Excel: SUBSTITUTE or REPLACEIn Excel, use SUBSTITUTE to replace matching text inside a cell, or REPLACE to replace characters at a…
- How to Filter Multiple Items in Excel: A Clear GuideTo filter for several items in Excel, open the filter arrow in the column heading, clear Select All,…
- Excel Rank Formula for Scores: How to Rank ResultsTo rank scores in Microsoft Excel for Microsoft 365, use =RANK.EQ(B2,$B$2:$B$6,0) when the score to rank is in…
- Excel Align Left Shortcut: Windows and Mac GuideIn Excel for Microsoft 365 on Windows, select the cell or range and press Alt, then H, A,…
- Excel Conditional Formatting Formula Examples That WorkExcel conditional formatting formulas let you highlight cells or entire rows when a logical test is true. In…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.