In MySQL, you can use the INSERT() function to insert a string into another string.

You can either replace parts of the string with another string (e.g. replace a word), or you can insert it while maintaining the original string (e.g. add a word). The function accepts 4 arguments which determine what the original string is, the position with which to insert the new string, the number of characters to delete from the original string, and the new string to insert.

Here’s the syntax:

INSERT(str,pos,len,newstr)

Where str is the original string, pos is the position that the new string will be inserted, len is the number of characters to delete from the original string, and newstr is the new string to insert.

Replace a Word

Here’s an example where I use INSERT() to replace a word within a string:

SELECT INSERT('Cats and dogs', 6, 3, 'like');

Result:

Cats like dogs

This effectively replaces the word and with the word like. I used 6 because the word and started at the 6 character mark, and I used 3 because that’s how many characters I want to delete (the word and is 3 characters long).

Insert a Word

Here I simply insert a word without deleting anything from the original string:

SELECT INSERT('Cats and dogs', 10, 0, 'big ');

Result:

Cats and big dogs

The reason this doesn’t delete anything from the original string is because I specified 0 (which means zero characters should be deleted).

Out of Range Values

If you specify a position that’s outside the length of the original string, MySQL will return the original string unchanged.

Example:

SELECT INSERT('Cats and dogs', 20, 4, 'rabbits');

Result:

Cats and dogs

Here’s another example where I use a negative starting position:

SELECT INSERT('Cats and dogs', -1, 4, 'rabbits');

Result:

Cats and dogs

This is one of the differences between MySQL’s INSERT() function and Transact-SQL‘s STUFF() function. In T-SQL, the STUFF() function would return NULL in these cases.

Inserting NULL Values

Another area where MySQL’s INSERT() differs to T-SQL’s STUFF() is with NULL values. If you try to insert a NULL value, MySQL will return NULL.

SELECT INSERT('Cats and dogs', 6, 3, NULL);

Result:

NULL