MySQL INSERT() Function

Summary: in this tutorial, you will learn how to use the MySQL INSERT() function to replace a substring within a string with a new substring.

Introduction to MySQL INSERT() function

The INSERT() function allows you to replace a substring within a string with a new substring.

Here’s the basic syntax of the INSERT() function:

INSERT(str, pos, len, newstr)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• str: The original string where you want to carry the replacement.
• pos: The position in the original string where you want to start the replacement.
• len: The length of the substring to be replaced.
• newstr: The new string that you want to replace the old substring.

The INSERT() function returns the string str, with the substring starting at position pos and len characters long replaced by the string newstr.

If the length (len) is greater than the remaining length of the string after the starting position (pos), the INSERT() function will replace the characters from the starting position (pos) to the end of the string with the new substring.

The INSERT() function returns the original string if pos is not within the length of the string.

The INSERT() function also returns NULL if any argument is NULL.

The INSERT() function is multibyte safe. This means that it can correctly handle strings containing multibyte characters, making it suitable for use in various language settings.

Note that the INSERT() function doesn’t modify the original string; Instead, it returns a new string with the specified modifications.

MySQL INSERT function examples

Let’s take some examples of using the MySQL INSERT() function.

1) Basic MySQL INSERT() function example

The following example uses the INSERT() function to replace the substring SQL in the string MySQL with the new substring Database:

SELECT
  INSERT('MySQL', 3, 8, 'Database');Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------------------------+
| INSERT('MySQL', 3, 8, 'Database') |
+-----------------------------------+
| MyDatabase                        |
+-----------------------------------+1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

2) The position out of rage example

The following example uses the INSERT() function with the position that is out of range (negative in this case), the INSERT() function returns the original string as-is:

SELECT
  INSERT('MySQL', -1, 2, 'DB');Code language: SQL (Structured Query Language) (sql)

Output:

+------------------------------+
| INSERT('MySQL', -1, 2, 'DB') |
+------------------------------+
| MySQL                        |
+------------------------------+1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

3) The length exceeds the remaining string

The following example uses the INSERT() function but with a position that exceeds the remaining length of the string, therefore, it replaces the rest of the string from the position:

SELECT INSERT('MySQL', 3, 50, 'DB');Code language: SQL (Structured Query Language) (sql)

Output:

+------------------------------+
| INSERT('MySQL', 3, 50, 'DB') |
+------------------------------+
| MyDB                         |
+------------------------------+1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

4) Using the INSERT() function with table data

We’ll use the products table from the sample database.

The following example uses the INSERT() function to replace the rest of the string from position 4 with the character ”:

SELECT
  productCode,
  INSERT(productCode, 4, 10, '')
from
  products;Code language: SQL (Structured Query Language) (sql)

Output:

+-------------+--------------------------------+
| productCode | INSERT(productCode, 4, 10, '') |
+-------------+--------------------------------+
| S10_1949    | S10                            |
| S10_4757    | S10                            |
| S10_4962    | S10                            |
| S12_1099    | S12                            |
| S12_1108    | S12                            |
| S12_3148    | S12                            |
| S12_3380    | S12                            |
| S12_3891    | S12                            |
| S12_3990    | S12                            |
| S12_4675    | S12                            |
| S18_1129    | S18                            |
...Code language: SQL (Structured Query Language) (sql)

Summary

• Use the INSERT() function to replace a substring within a string with a new substring.