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 TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 474 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.