MySQL INSERT Function

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.

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

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

MySQL String INSERT Function Example

The following query shows multiple ways to use this method to insert a substring inside a sentence or text 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.

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 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 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