MySQL – How INSERT() Function Works for MySQL

You know there is an INSERT statement which is used to add data to a table. But did you know that there is an INSERT function which will replace certain characters within a string?

INSERT() function in MySQL is used to insert a substring at the specified position up to the specified number of characters.

Let us consider this example.

set @str:='This is nothing';

The variable @str has a string value “This is nothing”. Suppose you want to change “nothing” into “everything” in this string. You can use INSERT() function as shown below

select INSERT(@str,9,2,'every');

The INSERT() function replaces the characters starting from the position 9 to next 2 characters by “everything”. So in the string part “nothing”, no is replaced by “every” and the full string becomes “This is everything” .

You can also add a string between two strings as shown below

set @str:='This is nothing';
 select INSERT(@str,9,0,'not ');

The result is “This is not nothing”. When the third parameter is 0, it will not replace any characters, but append it at the starting position defined in the second parameter

Note: This INSERT() function is functionally equivalent to SQL Server’s STUFF() function. You can refer the usage of STUFF() function in this post.

Reference: Pinal Dave (https://blog.sqlauthority.com)