SQL Server 2012 Comma Parsing

Question

I'm trying to parse some comma separated values from a column in SQL Server 2012 while still keeping the data from the columns in the left and to the right.

I have seen some similar topic solutions but none seemed to be what I am looking for.

I have this:

FirstName   LastName    userid   Regions          ViewCosts HelpReviewCosts
---------------------------------------------------------------------
Darron      Peters      ya00003   All                y         y
John        Davies      ya30982   NA, EM, AP, LA     n         n

I am trying to parse the Regions column so that I can get this:

FirstName   LastName    userid   Regions          ViewCosts HelpReviewCosts
---------------------------------------------------------------------
Darron      Peters      ya00003   All                y         y
John        Davies      ya30982   NA                 n         n
John        Davies      ya30982   EM                 n         n
John        Davies      ya30982   AP                 n         n
John        Davies      ya30982   LA                 n         n

Possible duplicate of [SQL Server split CSV into multiple rows](http://stackoverflow.com/questions/9811161/sql-server-split-csv-into-multiple-rows) — Gurwinder Singh, Jan 03 '17 at 13:06
Why don't you *fix* the design problem instead? Use a *separate* table that links persons to regions. What you show here breaks even the 1st Normal Form. Once you have this, retrieving data in the shape you want requires a simple JOIN — Panagiotis Kanavos, Jan 03 '17 at 13:06
If you have control of the database schema, you should normalize the data instead of storing a list of CSVs. — elixenide, Jan 03 '17 at 13:07
[Split strings the right way – or the next best way - Aaron Bertrand](https://sqlperformance.com/2012/07/t-sql-queries/split-strings) — SqlZim, Jan 03 '17 at 13:17

score 2 · Accepted Answer · answered Jan 03 '17 at 13:34

There are thousands of examples on how to split/parse strings. Below are two samples, one with a UDF and the other without. Both use a CROSS APPLY

With a UDF

Declare @Yourtable table (FirstName varchar(25)   ,LastName varchar(25),userid  varchar(25),  Regions varchar(50), ViewCosts varchar(25), HelpReviewCosts varchar(25))
Insert Into @Yourtable values
('Darron','Peters','ya00003','All','y','y'),
('John','Davies','ya30982','NA, EM, AP, LA','n','n')

Select A.FirstName
      ,A.LastName    
      ,A.userid   
      ,Regions =B.RetVal
      ,A.ViewCosts 
      ,A.HelpReviewCosts
 From @Yourtable A
 Cross Apply [dbo].[udf-Str-Parse](A.Regions,',') B

Without A UDF

Select A.FirstName
      ,A.LastName    
      ,A.userid   
      ,Regions =B.RetVal
      ,A.ViewCosts 
      ,A.HelpReviewCosts
 From @Yourtable A
 Cross Apply ( 
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>'+ replace((Select A.Regions as [*] For XML Path('')),',','</x><x>')+'</x>' as xml).query('.')) as A 
                Cross Apply x.nodes('x') AS B(i)
      ) B

Both Returns

THE UDF if needed

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From  (Select x = Cast('<x>'+ replace((Select @String as [*] For XML Path('')),@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--Select * from [dbo].[udf-Str-Parse]('this,is,<test>,for,< & >',',')

score 0 · Answer 2 · answered Jan 03 '17 at 14:05

0

I suggest you to use STRING_SPLIT function

WITH 
CTE_Sample AS
(
    SELECT 'All'            AS txt
    UNION ALL
    SELECT 'NA, EM, AP, LA' AS txt
)

SELECT
    txt,
    value
  FROM CTE_Sample
  CROSS APPLY STRING_SPLIT(txt, ',');

answered Jan 03 '17 at 14:05

Arnaud Gastelblum

312
1
11

score 0 · Answer 3 · answered Jan 05 '17 at 11:12

If you don't want to 'udf' and 'string_split' function,then you can use this query.and it's suitable for large strings with comma separated and also much faster compared to others...

`CREATE TABLE TB (Number INT)
 DECLARE @I INT=0
 WHILE @I<1000
 BEGIN
 INSERT INTO TB VALUES (@I)
 SET @I=@I+1
 END
 SELECT 
    FirstName
    ,LastName
    ,userid
    ,S_DATA
    ,ViewCosts
    ,HelpReviewCosts
FROM    (
    SELECT
            FirstName
            ,LastName
            ,userid
            ,CASE   WHEN LEN(LIST2)>0 THEN LTRIM(RTRIM(SUBSTRING(LIST2, NUMBER+1, CHARINDEX(',', LIST2, NUMBER+1)-NUMBER - 1)))
                    ELSE NULL
            END AS S_DATA 
            ,ViewCosts
            ,HelpReviewCosts
           ,NUMBER
    FROM(
        SELECT  FirstName
                ,LastName
                ,userid
                ,','+Regions+',' LIST2
                ,ViewCosts
                ,HelpReviewCosts
                FROM Tb1
        )DT 
         LEFT OUTER JOIN TB N ON (N.NUMBER < LEN(DT.LIST2)) OR (N.NUMBER=1 AND DT.LIST2 IS NULL)
        WHERE SUBSTRING(LIST2, NUMBER, 1) = ',' OR LIST2 IS NULL
     ) DT2
     WHERE S_DATA<>''

this is my Output

SQL Server 2012 Comma Parsing

3 Answers3