Cloud

replace

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.

Redpanda SQL also supports the regexp_replace() function. It searches and replaces a substring that matches with a POSIX regular expression

Syntax

The syntax for replace() function is:

REPLACE(string, old_substring, new_substring)

The replace() function performs a case-sensitive replacement

Parameters

The syntax requires these parameters:

  • string: String to replace.

  • old_substring: Substring to replace. All occurrences in the string are replaced.

  • new_substring: New substring that will replace the old one.

Examples

Basic usage

This example demonstrates a basic usage of the replace() function.

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

The replace() function finds all occurrences of the ‘New’ substring in the ‘NewDatabase’ string and replaces it with the ‘Redpanda’ substring, producing:

+---------------------+
| f                   |
+---------------------+
| RedpandaDatabase    |
+---------------------+

Replace specified values in a table

This example shows how to replace the values of a specific column in a table. First, create a new table named hobby with club and category columns and insert the values into the respective columns.

CREATE TABLE hobby (
  club text,
  category text
);
INSERT INTO hobby
    (club, category)
VALUES
    ('Bridge','group'),
    ('Painting','individual'),
    ('Basketball','group'),
    ('Volleyball','group');

After that is completed, retrieve all values from the table using this query:

SELECT * FROM hobby;
+------------+---------------+
| club       | category      |
+------------+---------------+
| Bridge     | group         |
| Painting   | individual    |
| Basketball | group         |
| Volleyball | group         |
+--------------+-------------+

This query replaces the ‘group’ values in the category column with ‘sports’:

SELECT REPLACE(category, 'group', 'sports') from hobby;
+--------------+
| f            |
+--------------+
| sports       |
| individual   |
| sports       |
| sports       |
+--------------+

Remove a substring from a string

This example shows how to remove a substring from a string using the replace() function. In this case, the goal is to find all occurrences of the ‘Friends’ substring in the ‘Hello Friends’ string and remove it:

SELECT REPLACE('Hello Friends', 'Friends', '');
+-----------+
| f         |
+-----------+
| Hello     |
+-----------+

Replace multiple patterns

This example uses the replace() function to replace multiple patterns of the given string:

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

The replace() function is called multiple times to replace the corresponding string as specified:

  • [] into ()

  • {} into ()

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