MySQL INSERT() Function

In MySQL, The INSERT() function replace the substring specified by start position and lenth with a new string.

INSERT() Syntax

Here is the syntax of MySQL INSERT() function:

INSERT(str, pos, len, newstr)

Parameters

str
Required. The string to be processed.
pos
Required. The start position of the replaced substring.
len
Required. The length of the replaced substring.
newstr
Required. The new string to be inserted.

Return value

INSERT(str, pos, len, newstr) Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr.

  • If len is 0, INSERT() will insert newstr directly without replacement.
  • If pos is bigger than the length of str, INSERT() will return the original string.
  • If is len is bigger than the length of the remainder of the string from pos, INSERT() will replace the remainder of the string from pos.
  • The function will return NULL if any of parameters is NULL.

INSERT() Examples

Example 1

SELECT INSERT('Hello_World', 6, 1, ' ');
+----------------------------------+
| INSERT('Hello_World', 6, 1, ' ') |
+----------------------------------+
| Hello World                      |
+----------------------------------+

Example 2

  • The INSERT() function will return NULL if any of the arguments are NULL.
SELECT
    INSERT(NULL, 6, 1, ' ') null_1,
    INSERT('Hello_World', NULL, 1, ' ') null_2,
    INSERT('Hello_World', 6, NULL, ' ') null_3,
    INSERT('Hello_World', 6, 1, NULL) null_4;
+--------+--------+--------+--------+
| null_1 | null_2 | null_3 | null_4 |
+--------+--------+--------+--------+
| NULL   | NULL   | NULL   | NULL   |
+--------+--------+--------+--------+