How to use MySQL STUFF

The STUFF function is not a MySQL function and is specific to SQL Server. However, MySQL has a native function that works in the same way as the STUFF function. In this article, we will see how to use the MySQL INSERT function as an equivalent, what its parameters are for and some practical examples.

MySQL INSERT Function

The MySQL INSERT function adds a piece of text to the main string according to the parameters that we indicate.

How does it work? What parameters should I use to call it? We will find the answer to those questions below.

Syntax to execute INSERT function

A regular call to the INSERT function receives four parameters, namely the main string, start position, number, and new text.

1. INSERT(main-string, start-position, number, new-text)

INSERT or STUFF SQL examples

Example 1 – How to insert a substring at the end.

Imagine that we have the following string “We are going to use the INSERT function on this sentence.“. In this example, we want to replace all characters after the phrase “INSERT function” with an ellipsis “…”. We note that there are 39 characters that we want to keep (from the start of the sentence, up to the phrase “INSERT function“).

Parameters of example 1:

Parameters to execute example 1

Command to run

1. SELECT INSERT("We are going to use the INSERT function on this sentence.", 40, 18, "...");

Result:

We are going to use the INSERT function...

Example 2 – How to insert a substring at the beginning

Imagine we have the following string as the main-string: “INSERT function“. In this example, we are going to insert a substring (“STUFF function vs “) at the start of the main-string.

Parameters of example 2:

Parameters to execute example 2

Command to run

1. SELECT INSERT("INSERT function", 1, 0, "STUFF function vs");

Result:

STUFF function vs INSERT function

Example 3 – How to insert a substring in the middle of the main-string

Finally, let’s see how to insert a substring in the middle of “We are going to use the INSERT function on this sentence.”. Let’s try inserting the substring “MySQL ” in the middle of our main-string, right before the phrase “INSERT function“.

Parameters of example 3:

Parameters to execute example 3

Command to run

1. SELECT INSERT("We are going to use the INSERT function on this sentence.", 25, 0, "MySQL ");

Result:

We are going to use the MySQL INSERT function on this sentence.

Important considerations to keep in mind

1. The start-position parameter can go from 1 to the length of main-string.
2. If the start-position parameter is equal to 0, then INSERT function returns main-string.
3. If start-position > main-string length, then INSERT function returns main-string.