SUB_REPLACE
Description
The sub_replace function is used to replace substrings within a string. You can specify the substring to be replaced and the target string to replace it with. It returns a new string where the substring starting from start with length len in str is replaced by new_str. If start or len is a negative integer, it returns NULL. The default value for len is the length of new_str.
Syntax
sub_replace(<str>, <new_str>, [ ,<start> [ , <len> ] ])
Parameters
| Parameter | Description |
|---|---|
<str> | The target string in which the replacement will occur |
<new_str> | The string that will replace the specified substring |
<start> | start is the position where the replacement operation begins, indicating from which position in the string the replacement will start |
<len> | len is an optional parameter that specifies the length of the substring to be replaced |
Return Value
Returns the string after replacement.
Examples
- Basic usage: specify position and length replacement
SELECT sub_replace('doris', '***', 1, 2);
+-----------------------------------+
| sub_replace('doris', '***', 1, 2) |
+-----------------------------------+
| d***is |
+-----------------------------------+
- Using default length replacement
SELECT sub_replace('hello', 'Hi', 0);
+--------------------------------+
| sub_replace('hello', 'Hi', 0) |
+--------------------------------+
| Hillo |
+--------------------------------+
- Negative parameter returns NULL
SELECT sub_replace('hello', 'Hi', -1, 2);
+------------------------------------+
| sub_replace('hello', 'Hi', -1, 2) |
+------------------------------------+
| NULL |
+------------------------------------+
- NULL value handling
SELECT sub_replace(NULL, 'new', 0, 3);
+-------------------------------------+
| sub_replace(NULL, 'new', 0, 3) |
+-------------------------------------+
| NULL |
+-------------------------------------+
- UTF-8 string
SELECT sub_replace('doris', 'ṛìḍḍ', 1, 2);
+-------------------------------------------+
| sub_replace('doris', 'ṛìḍḍ', 1, 2) |
+-------------------------------------------+
| dṛìḍḍis |
+-------------------------------------------+
- Start position exceeds string length
SELECT sub_replace('hello', 'Hi', 9, 2);
+----------------------------------+
| sub_replace('hello', 'Hi', 9, 2) |
+----------------------------------+
| NULL |
+----------------------------------+
- Specified replacement length exceeds remaining string length
SELECT sub_replace('hello', 'Hi', 1, 9);
+----------------------------------+
| sub_replace('hello', 'Hi', 1, 9) |
+----------------------------------+
| hHi |
+----------------------------------+
Keywords
SUB_REPLACE, REPLACE
