The MySQL INSERT Function is one of the String Function, which is useful to insert or replace existing characters with a completely new string. This MySQL String Insert function uses the position argument to start replacing.

MySQL INSERT Function Syntax

The basic syntax of the Insert String in MySQL is as shown below.

INSERT(String, Position, Length, New_String)
  • String: Actual string
  • Position: At which position you want to insert a New_String
  • Length: Number of characters that you want to replace inside a String.
  • New_String: New string or substring that you want to insert.

MySQL String INSERT Example

The following query shows multiple ways to use this MySQL string insert function to insert a substring inside a string with an example.

First, We inserted or replaced o in 4th position with M. The second statement replace ia in 6th position with HI because length is 2. Within the third statement, we replaced the characters starting at 9th position and up to 11 characters with Hello World string.

SELECT INSERT('tutorialgateway', 4, 1, 'M');

SELECT INSERT('tutorialgateway', 6, 2, 'HI');

SELECT INSERT('tutorialgateway', 9, 11, 'Hello World ');
MySQL INSERT Function 1

In this String Function example, we used the non-existing positions such as 0 and -1 in the last two statements. As you can see, MySQL do nothing but printing the original string.

SELECT INSERT('Hello World', 1, 3, 'May');

SELECT INSERT('Hello World', 0, 3, 'May');

SELECT INSERT('Hello World', -1, 3, 'May');
MySQL INSERT Function 2

It is another example of this String insert function.

SELECT INSERT('tutorialgateway', 4, 5, 'Hello World');

SELECT INSERT('tutorialgateway', 4, 2, 'Hello World');

SELECT INSERT('tutorialgateway', 4, 20, 'Hello World');
MySQL INSERT Function 3