In MariaDB, INSERT() is a built-in string function that allows you to insert a substring into another string.

Syntax

The syntax goes like this:

INSERT(str,pos,len,newstr)

Where str is the string, pos is the starting position for the insert, len is the number of characters to replace, and newstr is the substring to insert.

Example

Here’s a basic example:

SELECT INSERT('The hot sun', 5, 3, 'red');

Result:

+------------------------------------+
| INSERT('The hot sun', 5, 3, 'red') |
+------------------------------------+
| The red sun                        |
+------------------------------------+

Here I replaced the word hot with the word red.

Below are more examples to demonstrate how the position and length arguments can affect the outcome.

SELECT 
    INSERT('The hot sun', 5, 0, 'red ') AS "1",
    INSERT('The hot sun', 5, 3, 'black hole') AS "2",
    INSERT('The hot sun', 1, 7, 'Black hole') AS "3";

Result:

+-----------------+--------------------+----------------+
| 1               | 2                  | 3              |
+-----------------+--------------------+----------------+
| The red hot sun | The black hole sun | Black hole sun |
+-----------------+--------------------+----------------+

Wrong Starting Position

If the starting position is outside the length of the string, the original string is returned.

SELECT 
    INSERT('The hot sun', 0, 3, 'red ') AS "1",
    INSERT('The hot sun', -5, 3, 'red') AS "2",
    INSERT('The hot sun', 20, 3, 'red') AS "3";

Result:

+-------------+-------------+-------------+
| 1           | 2           | 3           |
+-------------+-------------+-------------+
| The hot sun | The hot sun | The hot sun |
+-------------+-------------+-------------+

Long Length Arguments

If the length (third argument) is as long as, or longer than the remainder of the string, the remainder of the string is replaced with the substring.

Example:

SELECT 
    INSERT('The hot sun', 5, 10, 'red ') AS "1",
    INSERT('The hot sun', 9, 3, 'pavement') AS "2",
    INSERT('The hot sun', 9, 4, 'pavement') AS "3",
    INSERT('The hot sun', 1, 20, 'red') AS "4";

Result:

+----------+------------------+------------------+------+
| 1        | 2                | 3                | 4    |
+----------+------------------+------------------+------+
| The red  | The hot pavement | The hot pavement | red  |
+----------+------------------+------------------+------+

Null Arguments

Providing null for any of the arguments results in null:

SELECT 
    INSERT(null, 5, 10, 'red ') AS "1",
    INSERT('The hot sun', null, 3, 'pavement') AS "2",
    INSERT('The hot sun', 9, null, 'pavement') AS "3",
    INSERT('The hot sun', 1, 20, null) AS "4";

Result:

+------+------+------+------+
| 1    | 2    | 3    | 4    |
+------+------+------+------+
| NULL | NULL | NULL | NULL |
+------+------+------+------+

Providing the Wrong Number of Arguments

Calling INSERT() with the wrong number of arguments, or without passing any arguments results in an error:

SELECT INSERT();

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1