​
Overview

The REPLACE() function looks for and replaces a substring with a new one in a string. This function is often used to update the outdated or spelling mistakes in data that require an amendment.

Oxla also has the REGEXP_REPLACE() function. It enables you to search and replace a substring(s) that matches with a POSIX regular expression. See here for more info.

​
Syntax

The REPLACE() function syntax is described as follows.

REPLACE(string, old_substring, new_substring)

The syntax requires three arguments, explained below:

  • string: the string that you want to replace.

  • old_substring: the substring that you want to replace. All parts will be replaced if it appears multiple times in the string.

  • new_substring: the new substring that will replace the old one**.**

The REPLACE() function performs a case-sensitive replacement.

​
Examples

​
Case #1: Basic REPLACE() function

We will replace the substring of the “OxlaDatabase” string from “**New” **to “Oxla”, as shown below:

SELECT REPLACE ('NewDatabase', 'New', 'Oxla');

The REPLACE() function will find and replace all occurrences in the string and return the following:

+---------------------+
| f                   |
+---------------------+
| OxlaDatabase        |
+---------------------+

​
Case #2: Replacing the specified values in a table

This example shows how to replace the values of a specific column in a table.

  1. Create a new table named extracurriculars with club and category columns and insert the values into the respective columns.
CREATE TABLE hobby (
  club string,
  category string 
);
INSERT INTO hobby 
    (club, category) 
VALUES 
    ('Bridge','group'),
    ('Painting','individual'),
    ('Basketball','group'),
    ('Volleyball','group');
  1. Retrieve all values from the table using the following query.
SELECT * FROM hobby;

+------------+---------------+
| club       | category      |
+------------+---------------+
| Bridge     | group         |
| Painting   | individual    |
| Basketball | group         |
| Volleyball | group         |
+--------------+-------------+
  1. Replace the “group” value in the category column with** “sports”.**
SELECT REPLACE(category, 'group', 'sports') from hobby;
  1. The final result will look like the following. 
+--------------+
| f            |
+--------------+
| sports       |
| individual   |
| sports       |
| sports       |
+--------------+

​
Case #3: Remove a word with REPLACE() function

In the following example, we will remove **“Friends” **in a string with a REPLACE() function. 

SELECT REPLACE('Hello Friends','Friends','');

The final output will leave the remaining word, “Hello”.

+-----------+
| f         |
+-----------+
| Hello     |
+-----------+

​
Case #4: Replace multiple patterns with REPLACE() function

The following example uses the REPLACE() function to replace multiple patterns of the given string. 

SELECT REPLACE(REPLACE(REPLACE(REPLACE('2*[9-5]/{4+8}', '[', '('), ']', ')'), '{', '('), '}', ')');

We can see that the REPLACE function is called multiple times to replace the corresponding string as specified:

  • the [] into ()

  • the {} into ()

+------------------+
| f                |
+------------------+
| 2*(9-5)/(4-8)    |
+------------------+
POSIX Regular ExpressionsSTARTS_WITH