replace

Description

Replaces all occurrences of characters in a string with another string. This function performs a case-sensitive match when searching for pattern.

Syntax

VARCHAR replace(VARCHAR str, VARCHAR pattern, VARCHAR repl)

Parameters

  • str: the original string.

  • pattern: the characters to replace. Note that this is not a regular expression.

  • repl: the string used to replace characters in pattern.

Return value

Returns a string with the specified characters replaced.

If any argument is NULL, the result is NULL.

If no matching characters are found, the original string is returned.

Examples

-- Replace '.' in 'a.b.c' with '+'.

MySQL > SELECT replace('a.b.c', '.', '+');
+----------------------------+
| replace('a.b.c', '.', '+') |
+----------------------------+
| a+b+c                      |
+----------------------------+

-- No matching characters are found and the original string is returned.

MySQL > SELECT replace('a b c', '', '*');
+----------------------------+
| replace('a b c', '', '*') |
+----------------------------+
| a b c                      |
+----------------------------+

-- Replace 'like' with an empty string.

MySQL > SELECT replace('We like StarRocks', 'like', '');
+------------------------------------------+
| replace('We like StarRocks', 'like', '') |
+------------------------------------------+
| We  StarRocks                            |
+------------------------------------------+

-- No matching characters are found and the original string is returned.

MySQL > SELECT replace('He is awesome', 'handsome', 'smart');
+-----------------------------------------------+
| replace('He is awesome', 'handsome', 'smart') |
+-----------------------------------------------+
| He is awesome                                 |
+-----------------------------------------------+

keywords

REPLACE, replace

In this article
Copyright © 2024 CelerData