SQL Server STUFF Function

Summary: in this tutorial, you will learn how to use the SQL Server STUFF() function to delete a part of a string and then insert a substring into the string, beginning at a specified position.

SQL Server STUFF() function overview

The STUFF() function deletes a part of a string and then inserts a substring into the string, beginning at a specified position.

The following shows the syntax of the STUFF() function:

STUFF ( input_string , start_position , length , replace_with_substring )
Code language: SQL (Structured Query Language) (sql)

The STUFF() function accepts four arguments:

• input_string is the character string to be processed.
• start_position is an integer that identifies the position to start deletion and insertion. If start_position is negative, zero, or longer than the length of the string, the function will return NULL.
• length specifies the number of characters to delete. If the length is negative, the function returns NULL. If  length is longer than the length of the input_string, the function will delete the whole string. In case length is zero, the function will insert the replace_with_substring at the beginning of the input_string.
• replace_with_substring is a substring that replaces length characters of the input_string beginning at start_position.

SQL Server STUFF() function examples

Let’s take some examples of using the SQL Server STUFF() function.

A) Using STUFF() function to insert a string into another string at a specific Location

This example uses the STUFF() function to delete the first three characters of the string 'SQL Tutorial' and then insert the string 'SQL Server' at the beginning of the string:

SELECT 
    STUFF('SQL Tutorial', 1 , 3, 'SQL Server') result;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

result
-------------------
SQL Server Tutorial

(1 row affected)

B) Using the STUFF() function to convert time from HHMM to HH:MM

The following example uses the STUFF() function to insert the colon (:) at the middle of the time in the format HHMM and returns the new time value in the format HH:MM:

SELECT 
    STUFF('1230', 3, 0, ':') AS formatted_time;
Code language: SQL (Structured Query Language) (sql)

The output is:

formatted_time
--------------
12:30

(1 row affected)Code language: CSS (css)

C) Using the STUFF() function to format date from MMDDYYY format to MM/DD/YYYY:

The following example calls the STUFF() function twice to format a date from MMDDYYY to MM/DD/YYY:

SELECT 
    STUFF(STUFF('03102019', 3, 0, '/'), 6, 0, '/') formatted_date;
Code language: SQL (Structured Query Language) (sql)

The output of the statement is:

formatted_date
--------------
03/10/2019

(1 row affected)

D) Using the STUFF() function to mask credit card numbers

This example uses the STUFF() function to mask a credit card number. It reveals only the last four characters of the credit card no:

DECLARE 
    @ccn VARCHAR(20) = '4882584254460197';

SELECT 
    STUFF(@ccn, 1, LEN(@ccn) - 4, REPLICATE('X', LEN(@ccn) - 4))
    credit_card_no;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

credit_card_no
-----------------
XXXXXXXXXXXX0197

(1 row affected)

In this tutorial, you have learned how to use the SQL Server STUFF() function to delete a part of a string and insert a new substring, starting at a specified position.