Solved

SQL server splitting a comma separated value

Posted on 2016-09-03
3
21 Views
Last Modified: 2016-10-22
i want code for splitting a commaseparated list int a temporary table in sql server. can you help me
0
Comment
Question by:Gopakumar Pandarikkal
3 Comments
 
LVL 32

Accepted Solution

by:
Stefan Hoffmann earned 250 total points
Comment Utility
There are two built-in ways:

DECLARE @Text NVARCHAR(255) = N'1,2,<td>test</td>,4';

-- SQL Server 2016
SELECT	SS.*
FROM	STRING_SPLIT(@Text, ',') SS;

-- Before
WITH ToXml AS
	(
		SELECT	CAST(('<td><![CDATA[' + REPLACE(@Text, ',', ']]></td><td><![CDATA[') + ']]></td>') AS XML) AS XmlData
	)
	SELECT	td.value('.', 'NVARCHAR(255)') AS [value]
	FROM	ToXml
		CROSS APPLY	XmlData.nodes('td') A ( td );

Open in new window


The XML method is not optimal, but for normal purposes fast enough.
2
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 250 total points
Comment Utility
try this.
DECLARE  @s VARCHAR(100)

SELECT @s = 'Apples,Oranges,Mangoes,Grapes';

WITH cte
     AS (SELECT ROW_NUMBER()
                  OVER(ORDER BY p1.NUMBER) AS sr,
                NUMBER
         FROM   MASTER..spt_values p1
         WHERE  p1.TYPE = 'p'
                AND p1.NUMBER BETWEEN 1 AND LEN(@s) + 2
                AND SUBSTRING(',' + @s + ',',p1.NUMBER,1) = ',')
SELECT SUBSTRING(' ' + @s,a.NUMBER + 1,b.NUMBER - a.NUMBER - 1) AS Fruit
FROM   cte a
       INNER JOIN cte b
         ON b.sr = a.sr + 1

/*
Fruit
Apples
Oranges
Mangoes
Grapes
*/

Open in new window

0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now