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.