SQL STUFF

SQL STUFF function is used to delete a sequence of given length of characters from a string and inserting the given sequence of characters from the specified starting index.

SYNTAX

STUFF(string, start, length, new_string)

string – It is the string to be modified.

start  – The starting index from where the given length of characters will be deleted and new sequence of characters will be inserted.

length -The number of characters to delete from string. STUFF function will not not remove any characters from the string.

new_string -The new set of characters (string) to be inserted in place of deleted characters from the starting index.

 Lets look at an example of STUFF function in SQL Server.

Using STUFF function with starting position 1 removing nine characters and replacing a substring

Following example uses the STUFF function to delete the first nine characters from given string  ‘Microsoft @2018’ and then insert the string ‘Microsoft SQL Server’ at the beginning of the string .

DECLARE @string AS VARCHAR(25); 
SET @string = 'Microsoft @2018' 
SELECT STUFF(@string, 1, 9, 'Microsoft SQL Server') As output;

 

Using STUFF function with starting position 3 and removes zero characters

Following example uses STUFF function to insert the string ‘crosof’ at third position of given string as length is passed to zero, so it does not remove any character from given string.

Declare @string as varchar(30);
SET @string = 'MIT SQL SERVER @2018' 
SELECT STUFF(@string, 3 , 0, 'CROSOF') As output;

 

STUFF Function with negative Length value

If negative length values is passed to STUFF function, It returns null values as shown in below screenshot.

DECLARE @string AS VARCHAR(30); 
SET @string = 'Mit SQL Server @2018' 
SELECT STUFF(@string, 3, -1, 'crosof') AS [OUTPUT];

STUFF Function when starting Position is greater than string Length

SUTFF Function returns null when you provide a starting position that is greater than input string length.

The Length of input string @string =’SQL’ is three while the starting position value as second argument is passed 4. You can see the output returned as NULL by following statement.

DECLARE @string AS VARCHAR(30);
SET @string = 'SQL' 
SELECT STUFF(@string, 4, 0, 'Server') AS [OUTPUT];

Also Read..

SQL String Functions

UPPER()

LOWER()

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 1,584 total views,  1 views today