How to Insert a String into another String in SQL Server using STUFF()

by

In SQL Server, you can use the T-SQL STUFF() function to insert a string into another string. This enables you to do things like insert a word at a specific position. It also allows you to replace a word at a specific position.

Here’s the official syntax:

STUFF ( character_expression , start , length , replaceWith_expression )
  • character_expression is the original string. This can actually be a constant, variable, or column of either character or binary data.
  • start specifies the start position (i.e. where the new string will be inserted).
  • length is how many characters are to be deleted from the original string.
  • replaceWith_expression is the string that’s being inserted. replaceWith_expression can be a constant, variable, or column of either character or binary data.

Replace a Word

Here’s an example where I replace a word within a string.

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

Result:

Cats like dogs

In this case, I specify that the word like should be inserted at position 6, and that 3 characters should be deleted from the original string (this deletes the word and). So the word and is deleted and the word like is inserted.

Insert a Word

Here’s an example of inserting a word without deleting anything from the original string:

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

Result:

Cats and big dogs

Nothing is deleted from the original string because I specified 0 as the third argument.

Out of Range Values

If the start position is outside the length of the original string, a NULL value is returned.

Example:

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

Result:

NULL

In this case, the original string isn’t 20 characters long, so the result is NULL.

The same thing happens if you provide 0 or a negative number:

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

Result:

NULL

The result of these examples are in contrast to MySQL’s INSERT() function, which would’ve returned the original string in this scenario. MySQL’s INSERT() does basically the same thing as T-SQL’s STUFF() (except for cases like these).

Inserting NULL Values

If you insert a NULL value, your result will simply remove the stated number of characters from the stated position. In other words, nothing is inserted, but characters are still removed as specified.

Example:

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

Result:

Cats dogs

This is another area where MySQL differs to T-SQL. Doing this example with MySQL’s INSERT() function would have returned a result of NULL.