STUFF IN MS SQL, Getting data from table in a single row separated by comma or other sign

Posted on by

Consider following SQL statement for MS SQL

Select UserName from UserInfo

It will return

UserName
Anis
Hasibul

If you want to get these record in following way

UserName
Anis, Hasibul

Then you have to use STAFF
Consider following example of staff for getting above data

SELECT STUFF
(
	(Select TOP 100 PERCENT ',' + UserName from UserInfo FOR XML PATH(''))
	,1
	,1
	,' '
) as UserName

Basic Structure for STUFF Statement
STUFF ( char_exp, start, length, char_exp_to_stuff)
Consider other examples

select stuff('Hello Again',1,0, 'World')
-- WorldHello Again
select stuff('Hello Again',1,1, 'World')
-- Worldello Again
select stuff('Hello Again',7,0, 'World ')
-- Hello World Again
--Ex: Remove a character
select stuff('Hello*World',charindex('*','Hello*World'),1,'')
-- returns: Hello World