STUFF function is used to insert a string into another string. Basically, it deletes the characters from a source string and inserts another string at the specified position.
Syntax
Here, character_exp is an expression of the character data to be modified. Start is an integer, which specifies start position in character_exp to delete and insert another string (i.e. replacewith_exp) from here and length is an integer, which specifies the number of characters to be deleted. Replacewith_exp is a character expression to be inserted at the start position.
Example 1
- Select STUFF('www.google.com',5,6,'c-sharpcorner')
![]()
In the example, mentioned above, we have specified the location to delete(i.e. 5) and delete 6 characters from the start position and add another string in this place.
Example 2
![]()
In the second example, we have inserted one string between another string.
Example 3
Masking Credit Card Number using STUFF function
![]()
In the example, mentioned above, we have masked all the numbers except the last four of a credit card number.
Example 4
STUFF function returning Null Value
If start position is 0, a null value is returned. If start position is greater than the length of initial character expression, the null value is returned. If length(integer parameter) of the string to delete is negative, a null value is returned.
![]()