OVERLAY
Description
The OVERLAY function is used to replace a substring at a specified position and length within a string. Starting from the specified position, it replaces the specified length of characters with a new string.This function is multibyte safe.
This function behaves consistently with the INSERT function in MySQL.
Alias
- INSERT
Syntax
OVERLAY(<str>, <pos>, <len>, <newstr>)
Parameters
| Parameter | Description |
|---|---|
<str> | The original string to be modified. Type: VARCHAR |
<pos> | The starting position for replacement (1-based). Type: INT |
<len> | The length of characters to replace. Type: INT |
<newstr> | The new string to use for replacement. Type: VARCHAR |
Return Value
Returns VARCHAR type, the new string after replacement.
Special cases:
- If any parameter is NULL, returns NULL
- If
<pos>is less than 1 or exceeds string length, no replacement occurs and returns the original string - If
<len>is less than 0 or exceeds the remaining length, replaces from<pos>to the end of the string
Examples
- Basic usage: replace middle part
SELECT overlay('Quadratic', 3, 4, 'What');
+------------------------------------+
| overlay('Quadratic', 3, 4, 'What') |
+------------------------------------+
| QuWhattic |
+------------------------------------+
- Negative length: replace to end
SELECT overlay('Quadratic', 2, -1, 'Hi');
+-----------------------------------+
| overlay('Quadratic', 2, -1, 'Hi') |
+-----------------------------------+
| QHi |
+-----------------------------------+
- Position out of bounds: no replacement
SELECT overlay('Hello', 10, 2, 'X');
+-------------------------------+
| overlay('Hello', 10, 2, 'X') |
+-------------------------------+
| Hello |
+-------------------------------+
- NULL value handling
SELECT overlay('Hello', NULL, 2, 'X');
+--------------------------------+
| overlay('Hello', NULL, 2, 'X') |
+--------------------------------+
| NULL |
+--------------------------------+
SELECT INSERT('🎉🎊🎈', 2, 1, '🎁');
+--------------------------------------+
| INSERT('🎉🎊🎈', 2, 1, '🎁') |
+--------------------------------------+
| 🎉🎁🎈 |
+--------------------------------------+
