If you’re familiar with SQL Server, you might know that you can use the T-SQL STUFF() function to insert a string within a string. As it turns out, MySQL has a similar function – but with a different name.
MySQL’s INSERT() function does essentially the same thing that the T-SQL STUFF() function does.
With the exception of a couple of minor differences (see below), both functions work exactly the same.
Syntax
Here’s the official syntax of each of these functions.
T-SQL – The STUFF() Function
STUFF ( character_expression , start , length , replaceWith_expression )
MySQL – The INSERT() Function
INSERT(str,pos,len,newstr)
Although each of these definitions use different terminology, they basically do the same thing.
Examples
Here’s an example of each of these functions in action.
T-SQL – The STUFF() Function
SELECT STUFF('Cats and dogs', 6, 3, 'like');
Result:
Cats like dogs
MySQL – The INSERT() Function
SELECT INSERT('Cats and dogs', 6, 3, 'like');
Result:
Cats like dogs
As you can see, the only difference is the function name. We can port one to the other simply by changing the name of the function.
Differences Between STUFF() and INSERT()
There are a couple of differences in how these functions work. In particular, the two main differences are how they deal with:
- Out of range positions
- NULL values
These differences are explained below.
Out of Range Positions
If you try to insert at a position that’s outside the length of the original string, MySQL’s INSERT() function will return the original string. On the other hand, T-SQL’s STUFF() function will return NULL.
T-SQL – The STUFF() Function
SELECT STUFF('Cats and dogs', 20, 4, 'rabbits');
Result:
NULL
MySQL – The INSERT() Function
SELECT INSERT('Cats and dogs', 20, 4, 'rabbits');
Result:
Cats and dogs
NULL Values
These two functions also differ in how they deal with any NULL values you try to insert.
T-SQL – The STUFF() Function
SELECT STUFF('Cats and dogs', 6, 3, NULL);
Result:
Cats dogs
T-SQL – The INSERT() Function
SELECT INSERT('Cats and dogs', 6, 3, NULL);
Result:
NULL