How To Convert Comma Seprated (CSV) List Into A Single Column Multiple Rows

• Tweet
• 

        // description of your code here

declare	@list nvarchar(max) = 'America, India, Pakistan, Iran, Irak, Australia';
declare	@Delimiter nvarchar(10) = ',';
DECLARE @LEN INT
SET @LEN=LEN(@List)+1
 
;With UserList AS
( 
	SELECT	
			cast(isNull(NULLIF(CHARINDEX(@Delimiter,@List,1),0),@LEN) as int) AS nEnd,
			RTRIM(LTRIM(SUBSTRING(@List,1,isNull(NULLIF(CHARINDEX(@Delimiter,@List,1),0),@LEN)-1))) AS Users
	UNION All
	SELECT	
			cast(isNull(NULLIF(CHARINDEX(@Delimiter,@List,nEnd+1),0),@LEN) as int),
			RTRIM(LTRIM(SUBSTRING(@List,nEnd+1,isNull(NULLIF(CHARINDEX(@Delimiter,@List,nEnd+1),0),@LEN)-nEnd-1)))
	FROM UserList
	WHERE nEnd<@LEN
)
SELECT NULLIF(Users,'') AS Users FROM UserList where 
--OUTPUT
America
India
Pakistan
Iran
Irak
Australia

<a href="http://www.java-forums.org/java-software/"><strong>Java Software</strong></a>    

Tags:
• server
• SQL