You must Sign In to post a response.
  • Category: SQL Server

    Split Column values to column in SQL

    Hi,

    I want to split the column values to column in SQL based on comma.

    Ex. I have the values in a column as 'CBE,CHN,VLR,NKL,SLM,TRY' and I want a result

    COL1 COL2 COL3 COL4 COLN
    CBE CHN VLR NKL ValueN

    How can get this in SQL server? I tried with Parsename but it's supports upto 4 split levels only not more than 4.

    Regards,
    Sathish S
  • #765149
    Hi,
    Try this:
    CREATE TABLE #test(id int, data varchar(100))
    INSERT INTO #test VALUES (1,'CBE,CHN,VLR,NKL,SLM,TRY')

    DECLARE @pivot varchar(8000)
    DECLARE @select varchar(8000)

    SELECT
    @pivot=coalesce(@pivot+',','')+'[col'+cast(number+1 as varchar(10))+']'
    FROM
    master..spt_values where type='p' and
    number<=(SELECT max(len(data)-len(replace(data,',',''))) FROM #test)
    SELECT
    @select='
    select p.*
    from (
    select id,substring(data, start+2, endPos-Start-2) as token,
    ''col''+cast(row_number() over(partition by id order by start) as varchar(10)) as n
    from (
    select id, data, n as start, charindex('','',data,n+2) endPos
    from (select number as n from master..spt_values where type=''p'') num
    cross join
    (
    select id, '','' + data +'','' as data
    from #test
    ) m
    where n < len(data)-1 and substring(data,n+1,1) = '','') as data
    ) pvt
    Pivot ( max(token)for n in ('+@pivot+'))p'
    EXEC(@select)

    Hope it helps.
    Regards,
    Shashikant Gurav
    shashikantgurav22@gmail.com

  • #765571
    Hi Sathish

    Try this

    DECLARE @table TABLE(Keywords varchar(100))
    insert into @table values('CBE,CHN,VLR,NKL,SLM,TRY')
    ;with cte as (
    select Keywords,substring(Keywords,0,charindex(',',Keywords))Key1
    ,SUBSTRING(Keywords,charindex(',',Keywords)+1,LEN(Keywords)-charindex(',',Keywords))Key2
    from @table
    ),cte1 as (
    select Keywords,Key1,substring(Key2,0,charindex(',',Key2))Key2
    ,SUBSTRING(Key2,charindex(',',Key2)+1,LEN(Key2)-charindex(',',Key2))Key3
    from cte
    ),cte2 as (
    select Keywords,Key1,Key2,substring(Key3,0,charindex(',',Key3))Key3
    ,SUBSTRING(Key3,charindex(',',Key3)+1,LEN(Key3)-charindex(',',Key3))Key4
    from cte1
    ),cte3 as (
    select Keywords,Key1,Key2,Key3,substring(Key4,0,charindex(',',Key4))Key4
    ,SUBSTRING(Key4,charindex(',',Key4)+1,LEN(Key4)-charindex(',',Key4))Key5
    from cte2
    )
    select Keywords,Key1,Key2,Key3,Key4,substring(Key5,0,charindex(',',Key5))Key5
    ,SUBSTRING(Key5,charindex(',',Key5)+1,LEN(Key5)-charindex(',',Key5))Key6
    from cte3

  • #765573
    Hi Sathish,

    Try CTE :Common Table Expression for this.

    Specifies a temporary named result set, known as a common table expression (CTE).
    This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.
    This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. A common table expression can include references to itself.
    This is referred to as a recursive common table expression.

    Here is an example for expected output:

    WITH Split_Fields (Field, xmlfields)
    AS
    (
    SELECT Field1 AS Field,
    CONVERT(XML,'<Fields><field>'
    + REPLACE(Field1,',', '</field><field>') + '</field></Fields>') AS xmlfields
    FROM Table1
    )

    SELECT Field,
    xmlfields.value('/Fields[1]/field[1]','varchar(100)') AS Field1,
    xmlfields.value('/Fields[1]/field[2]','varchar(100)') AS Field2,
    xmlfields.value('/Fields[1]/field[3]','varchar(100)') AS Field3,
    xmlfields.value('/Fields[1]/field[4]','varchar(100)') AS Field4,
    xmlfields.value('/Fields[1]/field[5]','varchar(100)') AS Field5
    FROM Split_Fields

  • #766390
    Hi

    you can go through this piece of code(Query)


    CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
    returns @temptable TABLE (items varchar(8000))
    as
    begin
    declare @idx int
    declare @slice varchar(8000)

    select @idx = 1
    if len(@String)<1 or @String is null return

    while @idx!= 0
    begin
    set @idx = charindex(@Delimiter,@String)
    if @idx!=0
    set @slice = left(@String,@idx - 1)
    else
    set @slice = @String

    if(len(@slice)>0)
    insert into @temptable(Items) values(@slice)

    set @String = right(@String,len(@String) - @idx)
    if len(@String) = 0 break
    end
    return
    end


    Calling Method


    Select * from Split('CBE,CHN,VLR,NKL,SLM,TRY',',')


    Output


    CBE
    CHN
    VLR
    NKL
    SLM
    TRY

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #766406
    There is no inbuilt SPLIT function exist in SQL you need to create a separate SQL function for it, see below snippet

    CREATE FUNCTION SplitString
    (
    @Input NVARCHAR(MAX),
    @Character CHAR(1)
    )
    RETURNS @Output TABLE (
    Item NVARCHAR(1000)
    )
    AS
    BEGIN
    DECLARE @StartIndex INT, @EndIndex INT
    SET @StartIndex = 1
    IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
    BEGIN
    SET @Input = @Input + @Character
    END
    WHILE CHARINDEX(@Character, @Input) > 0
    BEGIN
    SET @EndIndex = CHARINDEX(@Character, @Input)
    INSERT INTO @Output(Item)
    SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
    SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
    END
    RETURN
    END
    GO

    //call it as
    SELECT Item
    FROM dbo.SplitString('Apple,Mango,Banana,Guava', ',')

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #766768
    Hi,
    Go through the Below mentioned Queries. It will Guide you to do this.

    1.http://www.codeproject.com/Questions/784832/How-to-Split-a-Column-value-in-Sql-Server

    2.http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns

    3.http://stackoverflow.com/questions/5123585/how-to-split-a-single-column-values-to-multiple-column-values

  • #766796

    ALTER function dblmatrix(@Column varchar(30)) returns varchar(50)
    as
    Begin
    Declare @findex int
    Declare @lfindex int
    Declare @Beginse varchar(50)
    Declare @lastse varchar(50)
    Declare @lasts int
    Declare @BeginCount int
    declare @lngtrema int
    set @findex = patindex('%,%',@Column)
    set @Beginse = substring(@Column,@findex+1,len(@Column))
    set @lfindex = patindex('%,%',@Beginse)
    set @Beginse = substring(@Beginse,1,@lfindex-1)
    return @Beginse
    End

    SRI RAMA PHANI BHUSHAN KAMBHAMPATI


Sign In to post your comments