i want to split one column to several columns using delimiter(comma)

Question

I want to split one column into several columns using delimiter(comma). Please someone help on this one:

Values
--------------
352306055351667,LoginId,gangadaran.s,Password,1,VersionCode,38,DistributorId,158,OrganisationId,33,MobileDate,2014\/04\/04,13,40,10,UserId,7916,LastDayClose,,BranchId,158,DownloadedDataDate,2014\/04\/04

I want it as:

col1           | col2   | col3        | col4     | col5|........|  col23
-----------------------------------------------------------------------------
352306055351667|LoginId | gangadaran.s| Password | 1   |........|  2014\/04\/04

the original data was stored in a single column with comma delimiters?! — Takarii, Mar 04 '16 at 09:56
Possible duplicate of [How to split a comma-separated value to columns](http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) — ADreNaLiNe-DJ, Mar 04 '16 at 10:01
I think `LoginId: 352306055351667, Password: gangadaran.s, VersionCode: 1 , DistributorId: 38...` and etc. So string contains column names and data. — gofr1, Mar 04 '16 at 10:07
Maybe you need instead of `col1` and others use `Loginid, Password` etc? — gofr1, Mar 04 '16 at 10:20

score 0 · Answer 1 · answered Mar 04 '16 at 10:05

0

You can try this:

CREATE FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO

and then simply:

select * from dbo.fnSplit('352306055351667,LoginId,gangadaran.s,Password,1,VersionCode,38,DistributorId,158,OrganisationId,33,MobileDate,2014\/04\/04,13,40,10,UserId,7916,LastDayClose,,BranchId,158,DownloadedDataDate,2014\/04\/04', ',')

answered Mar 04 '16 at 10:05

Rahul Tripathi

168,305
31
280
331

Thanks,but i want that rows to show in different columns – Tamilmani Purushothaman Mar 04 '16 at 10:16
@TamilmaniP:- You can use the PIVOT function to convert the rows into columns. :) – Rahul Tripathi Mar 04 '16 at 10:21
pivot function returns null to all columns – Tamilmani Purushothaman Mar 04 '16 at 10:28
Thank you for your valuable reply. – Tamilmani Purushothaman Mar 07 '16 at 05:14

score 0 · Answer 2 · answered Mar 04 '16 at 10:58

To get a result from your question:

DECLARE @string NVARCHAR(MAX) =  '352306055351667,LoginId,gangadaran.s,Password,1,VersionCode,38,DistributorId,158,OrganisationId,33,MobileDate,2014\/04\/04,13,40,10,UserId,7916,LastDayClose,,BranchId,158,DownloadedDataDate,2014\/04\/04',
        @xml xml
select @xml = cast('<d><q>'+REPLACE(@string,',','</q><q>')+'</q></d>' as xml);

SELECT  n.v.value('q[1]','NVARCHAR(50)') as Col1,
        n.v.value('q[2]','NVARCHAR(50)') as Col2,
        n.v.value('q[3]','NVARCHAR(50)') as Col3,
        n.v.value('q[4]','NVARCHAR(50)') as Col4,
        n.v.value('q[5]','NVARCHAR(50)') as Col5,
        n.v.value('q[6]','NVARCHAR(50)') as Col6,
        n.v.value('q[7]','NVARCHAR(50)') as Col7,
        n.v.value('q[8]','NVARCHAR(50)') as Col8,
        n.v.value('q[9]','NVARCHAR(50)') as Col9,
        n.v.value('q[10]','NVARCHAR(50)') as Col10,
        n.v.value('q[11]','NVARCHAR(50)') as Col11,
        n.v.value('q[12]','NVARCHAR(50)') as Col12,
        n.v.value('q[13]','NVARCHAR(50)') as Col13,
        n.v.value('q[14]','NVARCHAR(50)') as Col14,
        n.v.value('q[15]','NVARCHAR(50)') as Col15,
        n.v.value('q[16]','NVARCHAR(50)') as Col16,
        n.v.value('q[17]','NVARCHAR(50)') as Col17,
        n.v.value('q[18]','NVARCHAR(50)') as Col18,
        n.v.value('q[19]','NVARCHAR(50)') as Col19,
        n.v.value('q[20]','NVARCHAR(50)') as Col20,
        n.v.value('q[21]','NVARCHAR(50)') as Col21,
        n.v.value('q[22]','NVARCHAR(50)') as Col22,
        n.v.value('q[23]','NVARCHAR(50)') as Col23,
        n.v.value('q[24]','NVARCHAR(50)') as Col24
FROM @xml.nodes('/d') AS n(v)

Results:

Col1                                               Col2                                               Col3                                               Col4                                               Col5                                               Col6                                               Col7                                               Col8                                               Col9                                               Col10                                              Col11                                              Col12                                              Col13                                              Col14                                              Col15                                              Col16                                              Col17                                              Col18                                              Col19                                              Col20                                              Col21                                              Col22                                              Col23                                              Col24
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
352306055351667                                    LoginId                                            gangadaran.s                                       Password                                           1                                                  VersionCode                                        38                                                 DistributorId                                      158                                                OrganisationId                                     33                                                 MobileDate                                         2014\/04\/04                                       13                                                 40                                                 10                                                 UserId                                             7916                                               LastDayClose                                                                                          BranchId                                           158                                                DownloadedDataDate                                 2014\/04\/04

(1 row(s) affected)

Thanks gofr1.i got it. – Tamilmani Purushothaman Mar 07 '16 at 05:13 — Tamilmani Purushothaman, Mar 07 '16 at 05:13

i want to split one column to several columns using delimiter(comma)

2 Answers2