The MySQL INSERT Function is one of the String functions which is useful to insert or replace existing characters with a completely new string. This String Insert function uses the position argument to start replacing.
The basic syntax of the MySQL Insert String function is as shown below.
INSERT(String, Position, Length, New_String)
- The actual text.
- Position: At which position do 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 the 4th position with M. The second statement replaces ia in the 6th position with HI because the length is 2. Within the third statement, we replaced the characters starting at the 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 ');
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 does nothing but print the original string.
SELECT INSERT('Hello World', 1, 3, 'May');
SELECT INSERT('Hello World', 0, 3, 'May');
SELECT INSERT('Hello World', -1, 3, 'May');
It is another example of this MySQL insert function.
SELECT INSERT('tutorialgateway', 4, 5, 'Hello World');
SELECT INSERT('tutorialgateway', 4, 2, 'Hello World');
SELECT INSERT('tutorialgateway', 4, 20, 'Hello World');
