SQL code to extract values separated by commas in a multivalued field

Question

How I can do this using SQL Server?

https://stackoverflow.com/questions/tagged/sql-server+split – Sep 15 '17 at 15:45 — , Sep 15 '17 at 15:45

score 5 · Answer 1 · answered Sep 15 '17 at 15:51

Here's one of the many, popular splitters.

declare @table table (accountnum int, [services] varchar(1000), PIN int)
insert into @table
values
(30200,'ASCF008,ASFTCTAF',111111),
(30200,'AFTCTAF',222222),
(30200,'AFTCTAF,ASCF004',555555)

Select
    accountnum
    ,[services] = Item
    ,PIN
from
    @table
cross apply DelimitedSplit8K([services],',')

THE SPLIT FUNCTION BY JEFF MODEN

/****** Object:  UserDefinedFunction [dbo].[DelimitedSplit8K]    Script Date: 09/15/2017 10:51:16 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[DelimitedSplit8K] (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!

RETURNS TABLE WITH SCHEMABINDING AS
RETURN

/* "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
enough to cover VARCHAR(8000)*/

  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;
GO

thanks @JasonA.Long, super powerful. I was just relooking at your tfn_tally this morning too — S3S, Sep 15 '17 at 16:34
Jeff actually has a similar Tally function that offers some slightly different functionality, that I'm likely going to include into my own at some point in the near future... Here's a SSC thread where he shares his version... https://www.sqlservercentral.com/Forums/FindPost1894973.aspx — Jason A. Long, Sep 15 '17 at 16:46
@VanessaTorres no problem at all. Most of the thanks goes to Jeff haha — S3S, Sep 15 '17 at 16:48
@scsimon - No problem. I figured you'd want to see both versions. — Jason A. Long, Sep 15 '17 at 16:57

score 1 · Answer 2 · edited Sep 15 '17 at 16:56

1

In SQL Server 2016 there is a STRING_SPLIT() function.

Reference here: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

As for earlier versions, great and simple answer is here: Turning a Comma Separated string into individual rows

edited Sep 15 '17 at 16:56

marc_s

732,580
175
1,330
1,459

answered Sep 15 '17 at 16:03

Karolina Ochlik

908
6
8

Finally Microsoft has got a nice function to Split strings. Dam I am still working on 2012. – mvisser Sep 15 '17 at 16:29

score 0 · Answer 3 · answered Sep 15 '17 at 15:51

Try this logic, it separates the commas.

SELECT Accountnum,
   LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS [Services],
   PTN
FROM
(
  SELECT 
    CAST('<XMLRoot><RowData>' + REPLACE([Services],',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
  FROM
  (
    SELECT   Accountnum,
      [Services],
      PTN  
    FROM dbo.TESTTABLE
  ) AS XMLData
) AS Result
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

score 0 · Answer 4 · answered Sep 15 '17 at 15:51

0

Your table design is wrong. You need an additional services table and an account services table:

tblAccount
AccountID
AccountNum
ServiceID
PTN

tblServices
ServiceID
Service

tblAccountService
AccountID
ServiceID

Your design is against First Normal Form

answered Sep 15 '17 at 15:51

SE1986

2,534
1
10
29

I agree, but is an inherited database.. trying to extract information with the current state that unfortunately is wrong. – Vanessa Torres Sep 15 '17 at 15:55
Is this the nature of your question? you are restructuring your database and you want to separate these into new rows? Or do you just want them to be new rows in that same table? – SE1986 Sep 15 '17 at 15:58
I just need to extract the data in individual records no redesign, for the moment. – Vanessa Torres Sep 15 '17 at 16:53

score 0 · Answer 5 · answered Sep 15 '17 at 17:12

0

I would use something like this.

Left of comma

SELECT LEFT(services,CHARINDEX(',',services)-1) FROM table

Right of comma

SELECT Right(services,CHARINDEX(',',REVERSE(services))-1) FROM table

answered Sep 15 '17 at 17:12

BrianMichaels

522
1
7
16

SQL code to extract values separated by commas in a multivalued field

5 Answers5