STUFF Function in Sql server

SQL Server String Functions

SQL Server string functions are scalar functions that perform an operation on a string input value and return a string or numeric value.

STUFF Function

The STUFF function inserts a set of characters into a given string at a given position. The syntax is:

STUFF(string to manipulate, starting position, length, characters to insert)
For example, the following query adds " town " string to every city in DimGeography table:

SELECT  STUFF(city, 5, 6, ' town ') AS Manipulated,  City  FROM dimGeography

Results:

Manipulated City
Rock town n Rockhampton
Town town  Townsville
Clov town  Cloverdale
Find town  Findon
Pert town  Perth

You saw how to find the position of a specific character or number of characters using CHARINDEX. Now you can apply that knowledge and use STUFF function to replace characters based on their position.

The following example determines the position of 'ville' in the City column and then replaces it with 'town':

SELECT STUFF(city, CHARINDEX('ville', city), 6, ' town ') AS Manipulated,  City 
FROM dimGeography  WHERE city LIKE '%ville'

Results:

Manipulated City
Campbells town  Campbellsville
Mel town  Melville
Cross town  Crossville
Mary town  Maryville
Nash town  Nashville