Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL server splitting a comma separated value

Posted on 2016-09-03
3
Medium Priority
?
49 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 35

Accepted Solution

by:
ste5an earned 1000 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 41

Assisted Solution

by:Sharath
Sharath earned 1000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

636 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