Oracle REPLACE function

Overview

The REPLACE function in Oracle is a versatile string manipulation tool used to replace all occurrences of a specified substring within a string with another substring. It is particularly useful for data cleaning and transformation tasks in SQL queries. If replacement_string is omitted or null, then all occurrences of search_string are removed. When search_string is null, then char is returned.

Syntax:

REPLACE(char, search_string
        [, replacement_string ]
       )

Parameters:

Return Value:

• The function returns a string of the same data type as the char parameter after replacing all instances of the search_string with the replacement_string.

Return Value Type

CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB

Applicable Versions

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Function Behavior

• Case Sensitivity: The REPLACE function is case-sensitive. Thus, the search_string must match the case in char for the replacement to occur.


• Null Handling: If search_string is NULL, the original string (char) is returned unchanged. If replacement_string is NULL, all occurrences of search_string are removed from char.

Visual Presentation

Examples: Oracle REPLACE function

Basic Replacement

Replace all instances of 'M' with 'F':

 -- Using the REPLACE function to substitute characters in a string
SELECT 
    -- Replace all occurrences of 'M' with 'F' in the string 'MAN and MAT'
    REPLACE('MAN and MAT', 'M', 'F') AS "New String"
FROM 
    -- Use the DUAL table, a special dummy table in Oracle, for single row operations
    DUAL;
 

Explanation:

• In this SQL query, we use the REPLACE function to substitute every occurrence of the letter 'M' with 'F' in the string 'MAN and MAT'.


• The query selects this modified string and labels it as "New String".


• The FROM DUAL clause indicates that we are using the special Oracle table DUAL, which is commonly used for operations that do not require data from any specific table.


• The result of this query will be 'FAN and FAT', reflecting the character replacements.

Sample Output:

New String
-----------
FAN and FAT

Removing a Substring

Remove all instances of 'the' from the sentence:

-- Using the REPLACE function to remove specific words from a string
SELECT 
    -- Replace all occurrences of the word 'the' with an empty string in 'Remove the unwanted words from the text'
    REPLACE('Remove the unwanted words from the text', 'the', '') AS "New String"
FROM 
    -- Use the DUAL table, a special dummy table in Oracle, for single row operations
    DUAL;
 

Explanation:

• In this SQL query, we utilize the REPLACE function to remove the word 'the' from the string 'Remove the unwanted words from the text'.


• The function replaces each occurrence of 'the' with an empty string (''), effectively removing it. The result is labeled as "New String".


• By specifying DUAL, we indicate that this operation is independent of any actual data table.


• The final output will be 'Remove unwanted words from text', where the word 'the' has been removed.

Sample Output:

New String                       |
---------------------------------+
Remove  unwanted words from  text|

Using REPLACE Without Replacement String

Remove spaces from a string:

-- Using the REPLACE function to remove spaces from a string
SELECT 
    -- Replace all occurrences of a space (' ') with an empty string ('') in 'Remove spaces in this text'
    REPLACE('Remove spaces in this text', ' ', '') AS "New String"
FROM 
    -- Use the DUAL table, a special dummy table in Oracle, for single row operations
    DUAL;
 

Explanation:

• This SQL query uses the REPLACE function to eliminate all spaces from the string 'Remove spaces in this text'.


• By replacing each space character (' ') with an empty string (''), we effectively remove all spaces in the text.


• The result, labeled as "New String", will be 'Removespacesinthistext', with all spaces removed.


• The query uses the DUAL table, which is a special placeholder table in Oracle, to perform this operation.

Sample Output:

New String            |
----------------------+
Removespacesinthistext|

Replacing Multiple Patterns

To replace multiple patterns in a string, you can nest REPLACE functions:

-- Using nested REPLACE functions to modify specific words in a string
SELECT 
    -- Replace the word 'quick' with 'slow' in the string 'The quick brown fox jumps over the lazy dog'
    REPLACE(
        -- First, replace 'quick' with 'slow' in the initial string
        REPLACE('The quick brown fox jumps over the lazy dog', 'quick', 'slow'),
        -- Then, replace 'lazy' with 'energetic' in the resulting string
        'lazy', 
        'energetic'
    ) AS "New String"
FROM 
    -- Use the DUAL table, a special dummy table in Oracle, for single row operations
    DUAL;
 

Explanation:

This SQL query demonstrates the use of nested REPLACE functions to perform multiple replacements within a single string.

• The first REPLACE function substitutes 'quick' with 'slow' in the string 'The quick brown fox jumps over the lazy dog', resulting in 'The slow brown fox jumps over the lazy dog'.


• The second REPLACE function then operates on this modified string, replacing 'lazy' with 'energetic', giving us 'The slow brown fox jumps over the energetic dog'.

The final transformed string, 'The slow brown fox jumps over the energetic dog', is returned as "New String". The DUAL table is used here as a dummy table to execute the query and get the result.

Sample Output:

New String                                     |
-----------------------------------------------+
The slow brown fox jumps over the energetic dog|

Combining with Other String Functions

You can combine REPLACE with other string functions like SUBSTR, INSTR, and LTRIM for complex string manipulations. For example, to replace the first occurrence of a word:

-- Using a combination of SUBSTR and INSTR functions to replace the first occurrence of a substring
SELECT 
    -- Extract the part of the string before the first occurrence of 'Hello'
    SUBSTR('Hello Hello World', 1, INSTR('Hello Hello World', 'Hello') - 1) ||
    -- Concatenate 'Hi' in place of the first 'Hello'
    'Hi' ||
    -- Extract the part of the string after the first occurrence of 'Hello'
    SUBSTR('Hello Hello World', INSTR('Hello Hello World', 'Hello') + LENGTH('Hello'))
    AS "New String"
FROM 
    -- Use the DUAL table for single row operations
    DUAL;
 

Explanation:

This SQL query uses a combination of SUBSTR and INSTR functions to replace the first occurrence of the substring 'Hello' with 'Hi' in the string 'Hello Hello World'.

• Finding the Position of 'Hello':


• The INSTR('Hello Hello World', 'Hello') function finds the position of the first occurrence of 'Hello', which is 1 in this case.


• Extracting the Part Before 'Hello':


• SUBSTR('Hello Hello World', 1, INSTR('Hello Hello World', 'Hello') - 1) extracts the part of the string before 'Hello'. Here, since 'Hello' is at the start, the result is an empty string.


• Concatenating 'Hi':


• 'Hi' is directly concatenated in place of 'Hello'.


• Extracting the Part After 'Hello':


• SUBSTR('Hello Hello World', INSTR('Hello Hello World', 'Hello') + LENGTH('Hello')) extracts the part of the string after 'Hello'. The LENGTH('Hello') is 5, so the position moves to after 'Hello'. The result is ' Hello World'.


• Combining the Parts:


• The parts are concatenated together to form 'Hi Hello World'.

The result of this operation is returned as "New String". The DUAL table is used to execute this query and produce the single-row result.

Sample Output:

New String
---------------------
Hi Hello World

Frequently Asked Questions (FAQ) - Oracle REPLACE Function

1. What is the Oracle REPLACE function used for?

The Oracle REPLACE function is used to replace all occurrences of a specified substring within a string with another substring. It's useful for modifying or cleaning up text data in SQL queries.

2. How does the REPLACE function handle case sensitivity?

The REPLACE function is case-sensitive. This means that the substring you want to replace must match the case in the original string exactly for the replacement to occur.

3. What happens if the search_string is NULL?

If the search_string parameter is NULL, the function returns the original string (char) unchanged.

4. What happens if the replacement_string is omitted or NULL?

If the replacement_string is omitted or NULL, the function removes all occurrences of the search_string from the original string (char).

5. What data types are supported by the REPLACE function?

The REPLACE function supports various string data types including CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

6. What are some common applications of the REPLACE function?

The REPLACE function is commonly used in data transformation tasks, such as:

• Replacing specific characters or words in a string.


• Removing unwanted substrings.


• Formatting text data to meet specific requirements.

7. Can the REPLACE function be used in conjunction with other functions?

Yes, the REPLACE function can be combined with other string functions like SUBSTR, INSTR, and LTRIM to perform more complex string manipulations.

8. Is the REPLACE function available in all versions of Oracle?

The REPLACE function is available in several versions of Oracle, including Oracle 12c, 11g, 10g, 9i, and 8i.

9. How does the REPLACE function handle empty strings?

If the search_string is found and the replacement_string is an empty string, the REPLACE function effectively removes the search_string from the original string.

10. What is the return value of the REPLACE function?

The function returns a modified string of the same data type as the original string (char), with all instances of the search_string replaced by the replacement_string.

11. Can we replace multiple patterns in a single call to REPLACE?

To replace multiple different patterns within a string, we can nest multiple REPLACE function calls. Each call can handle a different pattern to be replaced.

12. How does the REPLACE function handle large text data types?

For large text data types like CLOB or NCLOB, the REPLACE function operates similarly, allowing for the replacement of substrings within large text content efficiently.

Previous: REGEXP_SUBSTR
Next: RPAD