MySQL INSERT Function | Insert String Within a String

MySQL INSERT function is an inbuilt function in MySQL. It inserts a string within another string removing the corresponding characters from the original string. In this article, we will discuss the MySQL INSERT Function. Also, we will discuss a few examples of using it.

Syntax

INSERT(string, position, length, replaceString)

Parameters

The MySQL INSERT Function expects four parameters. Also, all the parameters are mandatory. The description of the parameters is as follows:

• string: The original input string.
• position: The starting position from where to replace in the original string.
• length: The number of characters to replace.
• replaceString: The replacement string.

Return Value

The MySQL INSERT Function returns the string after replacing the original characters with the replacement string. Also, consider the following cases:

• If the starting position is more than the length of the string, the function returns the original string.
• However, if the number of characters to replace is more than the rest of the string length, the function replaces the characters up to the end of the string.

Examples

Let’s consider a few examples of using the function:

Example 1: Simple Insert

For instance, consider a simple string to work with.

mysql> SELECT INSERT("Test String", 1, 2, "abcxyz") as finalString;
+-----------------+
| finalString     |
+-----------------+
| abcxyzst String |
+-----------------+

In the above example, the function replaces 2 characters starting from position 1 in the original string.

Similarly, the function replaces 5 characters starting from position 3 in the below example.

mysql> SELECT INSERT("Test String", 3, 5, "abcxyz") as finalString;
+--------------+
| finalString  |
+--------------+
| Teabcxyzring |
+--------------+

Example 2: Start Position Greater than Original String

However, if the start position is greater than the original string, the function returns the original string.

mysql> SELECT INSERT("Test String", 20, 5, "abcxyz") as finalString;
+-------------+
| finalString |
+-------------+
| Test String |
+-------------+

Example 3: Length of Characters Greater than Rest of the String

Likewise, if the length of the characters is more than the remaining length of the string, the function only removes the remaining characters.

mysql> SELECT INSERT("Test String", 9, 5, "abcxyz") as finalString;
+----------------+
| finalString    |
+----------------+
| Test Strabcxyz |
+----------------+

Conclusion

In conclusion, we discussed the MySQL INSERT Function. You can read more about it on the Official MySQL Documentation. Additionally, you can read about more MySQL String Functions on Concatly.