How is the stuff function used in SQL Server?

2 years ago
Isabella Edwards
1 minute

The STUFF function in SQL Server is used to replace specific characters or strings at a given position within a string.

Syntax:
STUFF ( character_expression , start , length , replaceWith_expression )

Explanation:

  1. The string to be modified.
  2. start: specifies the start position of the character or string to be replaced.
  3. length: the size of the character or string to be replaced.
  4. new character or string used for replacement.

Suppose we have a string ‘Hello, world!’ and we want to replace the comma with a period, we can use the STUFF function.

SELECT STUFF('Hello, world!', 6, 1, '.');

The result will return ‘Hello, world!’.

Another example is replacing the middle three characters in the string ‘12345’ with ‘abc’, which can be done using the STUFF function.

SELECT STUFF('12345', 2, 3, 'abc');

The result will return ‘1abc5’.

bannerAds