How to get comma (‘,’) separated string as SQL Server select results


We can do this in many ways, let’s discuss few of them.

Considering the below Employee table

image

1. STUFF and FOR XML PATH(‘’)

we can get comma separated string as sql server results with STUFF and FOR XML PATH(‘’) as below

SELECT STUFF((         select ','+ Name          from Employee         FOR XML PATH('')         )         ,1,1,'') AS Names


Output: Venkat,Anil,Mounika,Chandra,Raj,Dileep,Shyam

In the above query

select ','+ Name          from Employee         FOR XML PATH('')

returns the Employee names prefixed with comma like ,Venkat,Anil,Mounika,Chandra,Raj,Dileep,Shyam

So to remove the prefixed comma we are using STUFF.

Below is the STUFF syntax

STUFF(string, start, length, new_string)


Parameter Description
string This is Required field and The string to be stuffed or modified
start This is Required field and The start position of a string to be deleted
length This is Required field and The number of characters to delete from string
new_string This is Required field and The new string to insert into string at the start position

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s