What is the purpose of the STUFF function in SQL?

For an instructor lead, in-depth look at learning SQL click below.

Learn SQL >>

In SQL, one of the many functions we interact with frequently is the STUFF function. This function is utilized to insert a string into another string. It is a very handy function when dealing with strings and it is key in string manipulations.

Understanding the Syntax

The SQL Server STUFF function has the following syntax:

Here is what each part of it means:

• character_expression: This is the original string that will be modified.
• start: This indicates from where the function will start modifying the original string.
• length: This is the number of characters that will be deleted from the original string.
• replaceWith_expression: These are the characters that will be inserted into the original string.

Examples of the STUFF function

Let’s look at an example:

In the query above, we insert the string ‘ Server’ into the original string ‘SQL Tutorial’ beginning from the 5th character. Therefore, ‘ Server’ is inserted before ‘Tutorial’. The result string becomes ‘SQL Server Tutorial’.

STUFF function is also typically combined with other SQL functions. Here is an example of STUFF function with XML PATH method used to concatenate row values into a single string:

The query takes a list of names from the Product table, concatenates them with a comma using the XML PATH method, and then removes the first comma using the STUFF function.

In summary, the STUFF function in SQL is an excellent tool for string manipulation. It allows for the insertion of one string at a specific position in another string, making it particularly beneficial in data analytics and manipulation.