Solved

SQL server splitting a comma separated value

Posted on 2016-09-03
3
27 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 33

Accepted Solution

by:
ste5an earned 250 total points
ID: 41782808
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
ID: 41787495
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

808 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