Monday, March 4, 2013

SQL Server STUFF Function

In this post , I will explain the STUFF function of SQL Server of string function category.
In my previous posts, I explained UNICODE Function, LEFT Function, CHARINDEX Function, CHAR Function, ASCII Function, Simple script to backup all SQL Server databases, Table-Valued Parameters and some other articles related to SQL Server.

Here, I am going to explain STUFF function of SQL Server.

STUFF() Function

This function is used to delete the specified length of characters from input string and insert a another set of characters from specified starting point in input string
 
Syntax- STUFF(character_expression1, start, length, character_expression2)
Arguments-
  • character_expression1 : An expression of character data.
  • start : An int value, which specified the starting point in character_expression1. If start is longer than length of character_expression1 then a Null string is returned
  • length : An int value, which specified the length of characters to delete. If length is longer than length of character_expression1 then deletion occurs up to the last character in the last character_expression2
  • character_expression2 : An expression of character data, which will be insert in character_expression1.
Return Type- Returns character data.


Example-
 
Declare @nstring varchar(50)
set @nstring='Manish Dubey'
select Stuff(@nstring, 2,6,'.')
--Ouput M.Dubey

Note- If start or length is negative then a Null string is returned.

No comments:

Post a Comment

^ Scroll to Top