none
How to split string into row's data? RRS feed

  • Question

  • I have data like :

    col1  col2

    1      ab

    2      ba

    3      bc

    I want to put these data into rows like:

    col1 col2

    1      a

    1      b

    2      b

    2      a

    3      b

    3      c

    Thanks in advance.

    I am using SQL server 2005.
    • Edited by Anshul15 Tuesday, June 4, 2013 6:06 AM
    • Moved by SSISJoostMVP Tuesday, June 4, 2013 9:45 AM Not SSIS related.
    Tuesday, June 4, 2013 6:04 AM

Answers

All replies

  • Hi,

    Assuming col2 contains only 2 characters.

    select col1, left(col2, 1) from table1
    union
    select col1, right(col2, 1) from table1

    Use "Union All" if there are entries like "aa", "bb" or "cc".

    Hope this helps.
    ~J.



    Tuesday, June 4, 2013 6:35 AM
  • Hi Jonathan,

    It is not exact one as there is some of data are like 'abc' or 'abcd' then we need:

    1   a

    1   b

    1   c or

    2  a

    2  b

    2  c

    2  d.

    Thanks for your effort.

    Tuesday, June 4, 2013 6:41 AM
  • Here are two examples:
    http://beyondrelational.com/modules/2/blogs/106/posts/11126/ssis-script-component-split-single-row-to-multiple-rows.aspx

    http://microsoft-ssis.blogspot.com/2012/11/split-multi-value-column-into-multiple.html
    They both use a split that splits on a char, but since you don't have that you should be using something like this:

    char[] Students = Row.Students.ToCharArray();
    // or
    string[] Students = Regex.Split(Row.Students.ToString(), string.Empty);
    
    


     


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Tuesday, June 4, 2013 7:10 AM
  • Please anyone help me out to get desired output as mentioned above !!!

    I tried to make split function which splits the string into rows but it is not working as desired.

    Function:

    CREATE FUNCTION [dbo].[split]
    (  @ipRowData VARCHAR(4000))
    RETURNS
    @rtnValue table
    (
       
    Data VARCHAR(1000)
    )
    AS
    BEGIN
      
    DECLARE  @cnt varchar(2000)

       Declare  @itemStr varchar(200)
       
    Set @cnt = @ipRowData 
      While Datalength(@cnt)>0

       BEGIN

         If Len(@cnt )>0
         Begin

          SET @itemStr= Substring(@cnt,1,1)

          SET @cnt = Substring(@cnt,1+1, (Datalength(@cnt) -1 +1))
         
    INSERT @rtnValue  ( data ) Values (@itemStr)

          END

         ELSE BEGIN

         INSERT @rtnValue (data) Values (@cnt)
         Break

    END

    Query is how to pass col2's value to this function?

    select 1,data from [dbo].split(col2)


    • Edited by Anshul15 Tuesday, June 4, 2013 9:24 AM
    Tuesday, June 4, 2013 9:20 AM
  • Do you use SSIS or just TSQL?


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Tuesday, June 4, 2013 9:36 AM
  • Just TSQL.

    Thanks,

    Anshul

    Tuesday, June 4, 2013 9:41 AM
  • Try this,

    declare @t table (col1 int, col2 nvarchar(10))
    insert into @t values(1,'ab')
    insert into @t values(2,'xyz')
    ----------------------- query
    ;with cte as
    (
      select col1,
             substring(col2, 1, 1) as Chars,
             stuff(col2, 1, 1, '') as col2,
             1 as RowID
      from @t
      union all
      select col1,
             substring(col2, 1, 1) as Chars,
             stuff(col2, 1, 1, '') as col2,
             RowID + 1 as RowID
      from cte
      where len(col2) > 0
    )
    select col1, Chars
    from cte
    order by col1, RowID


    Regards, RSingh

    • Proposed as answer by Jonathan Quek Tuesday, June 4, 2013 11:13 PM
    Tuesday, June 4, 2013 10:16 AM
  • You can try using Table values functions and CROSS APPLY, 
    go
    CREATE FUNCTION [dbo].[Split](@String varchar(MAX))       
    returns @temptable TABLE (items varchar(2))       
    as       
    begin      
        declare @idx int       
        declare @slice varchar(2)       
    
        select @idx = len(@String)       
            if len(@String)<1 or @String is null  return       
    
        while @idx<>0       
        begin       
               
            if @idx!=0       
                set @slice = left(@String,1)       
            else       
                set @slice = @String       
    
            if(len(@slice)>0)  
                insert into @temptable(Items) values(@slice)       
    
            set @String = right(@String,len(@String) - 1)       
            if len(@String) = 0 break    
            set @idx=@idx-1   
        end   
    return 
    end;
    go
    
    create table #test (c1 int,c2 varchar(10))
    insert into #test values (1,'abc'),(2,'abcd')
    
    select c1,items from #test
    cross apply [dbo].[Split] (c2)


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, June 4, 2013 10:39 AM
  • Hi Sarat,

    select c1,items from #test
    cross apply
    [dbo].[Split] (c2)

    Above code does not work. It returns the same data without splitting.

    Thanks,

    Anshul

    Tuesday, June 4, 2013 11:50 AM
  • Hi Sarat,

    select c1,items from #test
    cross apply
    [dbo].[Split] (c2)

    Above code does not work. It returns the same data without splitting.

    Thanks,

    Anshul

    Did you try with different input or example given by me?

    with the input given in example i got ,

    c1 items
    1 a
    1 b
    1 c
    2 a
    2 b
    2 c
    2 d

    What is the SQL version you are using?


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, June 4, 2013 12:44 PM
  • Hi Sarat,

    I have applied on my own database.

    I am using SQL Server 2005.

    Thanks in advance.

    Anshul

    Tuesday, June 4, 2013 5:15 PM
  • Hi Sarat,

    I have applied on my own database.

    I am using SQL Server 2005.

    Thanks in advance.

    Anshul

    It should be working in SQL Server 2005 as well.


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, June 5, 2013 5:19 AM
  • Just now tried in SQL Server 2005 instance, it returns as expected.

    Only the insert statement alone needs to be modified for 2005 version.

    go
    CREATE FUNCTION [dbo].[Split_hunt](@String varchar(MAX))       
    returns @temptable TABLE (items varchar(2))       
    as       
    begin      
        declare @idx int       
        declare @slice varchar(2)       
    
        select @idx = len(@String)       
            if len(@String)<1 or @String is null  return       
    
        while @idx<>0       
        begin       
               
            if @idx!=0       
                set @slice = left(@String,1)       
            else       
                set @slice = @String       
    
            if(len(@slice)>0)  
                insert into @temptable(Items) values(@slice)       
    
            set @String = right(@String,len(@String) - 1)       
            if len(@String) = 0 break    
            set @idx=@idx-1   
        end   
    return 
    end;
    go
    
    create table #test (c1 int,c2 varchar(10))
    insert into #test values (1,'abc')
    insert into #test values(2,'abcd')
    
    select c1,items from #test
    cross apply [dbo].[Split_hunt] (c2)



    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, June 5, 2013 5:21 AM
  • Hi,

    could anyone tell me how to split the string from a column to multiple rows where ; acts as delimiter in sql server management studio older version where cross apply string split() does not work.

    for example: INPUT:

    COL1 COL2  COL3
    1 ABC DEF;  LEMEO;  ROW1
    2 QWER; JILILI KIKI ROW2
    3 FERRE ROW3

    Output:

    COL1 COL2  COL3
    1 ABC DEF ROW1
    1 HIKE ROW1
    1 LEMEO ROW1
    2 QWER ROW2
    2 JILILI KIKI ROW2
    3 FERRE ROW3

    Saturday, August 1, 2020 6:58 PM
  • could anyone tell me how to split the string from a column to multiple rows where ;

    I guess we could if you start a new thread describing your problem from start to end. But don't piggyback on old threads.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, August 1, 2020 8:32 PM
  • Hi Erland,

    I have similar question to above except it is not a single character in my query, so i am following up in same thread where already the problem is aware of. And also in other threads i have seen people commenting to not to start new thread if its similar question and here you are saying "don't piggyback".

    So its different opinions from each individuals but i can follow only one.. 

    Best Regards

    Monday, August 3, 2020 7:01 AM