Sunday 29 April 2012

STUFF Function

  
In this article I am trying to illustrate a vary useful function of SQL Server called STUFF.

STUFF Function

Stuff is a T-SQL Function used to delete a specified length of characters within a string and replace with another set of characters.
  
Syntax:

STUFF(Character_expression1, start, length, Character_expression2)

Character_expression1:

Represents the string in which the stuff function is to be applied.

Start:

It indicates the starting position of the character in the string of Character_expression1.

Length:

The length of the characters, which need to be replaced.

Charcter_expression2:

 It is the string that will be replaced to the start position.

Example:

SELECT STUFF('joydeep', 2, 3, 'xxxxx')

---------------
jxxxxxeep

Here "o" is the starting position 2, so from position 2 it takes 3 character "oyd" and replace it by "xxxxx".


Posted by: MR. JOYDEEP DAS

3 comments:

  1. Replies
    1. Hi Sir, can u help me on this,
      I want result without q

      select STUFF((
      select c.* from (
      Select (','+CompanyName ) as q From PersonWorkHistory Where PUID = '50253700'
      union all (select companyname from Companies where uid in (select top 1 companyuid from orders where UID
      in (select top 1 orderuid from Placements where PersonUID = '50253700')))) c
      FOR XML PATH('')
      ), 1, 0, ''
      )COMPANY

      Delete