Split Comma separated string without using cursor or while condition in sql Or Split comma separated string using XQuery

Split Comma separated string without using cursor or while condition in sql.

Split comma separated string using XQuery

Using below Query we can split comma separated string 

DECLARE @CommaSepString VARCHAR(max)

SET @CommaSepString='IND,UK,USA,AST,IND,PAK';

SELECT SplitCountry FROM (SELECT CAST('<A>' +Replace(@CommaSepString,',','</A><A>')+'</A>' AS XML) AS Strings

      )F1

      CROSS APPLY

      (

       SELECT Country.item.value('.','varchar(50)') AS SplitCountry

       FROM F1.Strings.nodes('A') AS Country(item)

      )F2

The out put looks like as below.

If you want to create reversely that mean table rows values to comma separated string look in below link.

http://mastermindsdotnet.blogspot.in/2013/07/create-comma-separated-string-from.html

Create Comma separated string from table rows value without using looping in SQL Or Create comma separated string from table rows values using XQuery

Create comma separated string from table rows value without using looping in SQL.

Or Create comma separated string from table rows values using XQuery.

Using below query we can achieve it.

In above example created one temporary table #Test. In the #Test temp table inserted some string values.

Using the below query you can get comma separated string.

SELECT STUFF(( SELECT DISTINCT  ',' + DataVal
                FROM #Test
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS [Comma Separated String];


If you want to do reversely that mean to split comma separated value see in below link..

http://mastermindsdotnet.blogspot.in/2013/07/split-comma-separated-string-without.html