Introduction
In this article, we are going to learn about the STUFF() function.
Use of STUFF function
- STUFF function deletes a sequence of characters from a source string and then inserts another sequence of characters into the source string, starting at a specified position.
- Delete the number of characters from the string. We need to define the number of characters using the length parameter. If we define zero, then it does not remove any characters from the string.
Syntax
STUFF( source_string, start_position, length, replace_with_substring )
- source_string is a string to modify.
- start_position is an integer that identifies the position to start deletion and insertion. If start_position is negative, zero, or greater than the length of the string, then the function will return NULL.
- length specifies the number of characters to delete. If the length is negative, then the function returns NULL.
- replace_with_substring is a substring that replaces length characters of the source_string beginning at start_position.
Let’s understand the STUFF function with some examples:
STUFF() function with starting position 5 and removes 4 characters and Stuff a substring
SELECT
STUFF('SQL Demo', 1 , 3, 'SQL Server') result;
STUFF() function to insert a string into another string at a specific Location
SELECT
STUFF ('The Code', 5, 4, 'Code Hubs') result;
STUFF() function to convert time from HHMM to HH:MM
SELECT
STUFF('1145', 3, 0, ':') AS time_formate;
STUFF() function to format date from MMDDYYY format to MM/DD/YYYY
SELECT
STUFF(STUFF('05082021', 3, 0, '/'), 6, 0, '/') date_formate;
STUFF() function to mask credit card numbers
DECLARE
@ccn VARCHAR(20) = '4882584254460185';
SELECT
STUFF(@ccn, 1, LEN(@ccn) - 4, REPLICATE('X', LEN(@ccn) - 4))
credit_card_no;
STUFF() function with a negative start position value
SELECT STUFF ('TheCodeHubs@', -2, 1, '.com') result;
Also check, Find Specific Text String In Stored Procedures, Functions, Views And Triggers In SQL Server
