21 March 2011 2 Comments

Using STUFF Function in SQL Server 2008

Stuff is a TSql Function which is used to delete a specified length of characters within a string and replace with another set of characters. The general syntax of STUFF is as below :

STUFF(character_expression1, start, length, character_expression2)Character_Expression1 represents the string in which the stuff is to be applied. start indicates the starting position of the character in character_expression1, length is the length of characters which need to be replaced. character_expression2 is the string that will be replaced to the start position.

Example:

Create sample table as shown below

CREATE TABLE [dbo].[Details](

[ID] [int] NULL,

[Name] [varchar](50) NULL,

[Location] [varchar](50) NULL

)

* I have not given primary constriant to the table in this example,

Insert some data into the table

For Example I have the data as shown below

ID    Name  Location

1     John  Bangalore

2     Sush  Hyderabad

3     Smith Chennai

1     John  Usa

2     Sush  NJ

3     Smith NY

1     John  Virginia

2     Sush  London

3     Smith Gulf

1     John  Delhi

2     Sush  SouthAfrica

3     Smith NorthCarolina

And now if we notice ” ID ” column,  1 is repeated twice having same Name but different location

WITHOUT USING STUFF FUNCTION

DECLARE @ID INT

SET @ID = 1

SELECT DISTINCT Name, Location

FROM dbo.Details D

WHERE D.ID= @ID

Without using stuff function we get the results in two different rows

OUTPUT:

Name    Location

John  Bangalore

John  Delhi

John  Usa

John  Virginia

If we want to concatenate and display output then easiest way is to use Stuff and get the result as follows

USING STUFF FUNCTION:

DECLARE @ID INT

SET @ID = 1

SELECT DISTINCT Name,

ISNULL(STUFF((SELECT DISTINCT ‘; ‘ + Location as[text()]

FROM dbo.Details

WHERE ID = D.ID

FOR XML PATH(”) , TYPE).value(‘.’,'varchar(max)’),1,1,”), ”) AS Location

FROM dbo.Details D

WHERE D.ID= @ID

OUTPUT :

Name    Location

John     Bangalore; Delhi; Usa; Virginia

2 Responses

  1. Jessie Brod 22 May 2011 11:25 pm #

    Hello admin. You will find this could be interesting for you.

  2. Eli Naccari 24 May 2011 12:01 am #

    Howdy site owner. Bless you for that blog post. Evaluate the site i mentioned.