none
Split one column data into multiple columns

    Question

  • I want to split data from one columns into three columns. Example of the data i am working with

    ColumnA

    1235-A 1231-GD 234-F

    12343

    1213-F 343-E

     

    I want to capture only the numbers and put them in three columns. For example

     

    ColumnA

    1235-A 1231-GD 234-F

     

    ColumnX       ColumnY         ColumnZ

    1235             1231              234

     

    Thanks in advance!

    Hardik

    Friday, April 15, 2011 8:19 PM

Answers

  • You need to first split your data into rows based on the space, then you can extract number only from each part using this blog post

    Extracting numbers with SQL Server

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by KJian_ Thursday, April 21, 2011 7:15 AM
    Friday, April 15, 2011 8:50 PM
  • The following block shows you how to separate the Column into three columns.

    If there are enough values for Col2 and Col3 then they are set as NULL.

     

    After that you can extract number as per the link given by Naomi.

     

    declare @Table table (Col varchar(30))
    
    insert @Table select '1235-A 1231-GD 234-F' union
    select '12343' union
    select '1213-F 343-E'
    
    select Col1 = case when Col like '% %' then left(Col, charindex(' ', Col) - 1) else Col end,
      Col2 = case when Col like '% % %' then left(substring(Col, charindex(' ', Col) + 1, 30), charindex(' ', substring(Col, charindex(' ', Col) + 1, 30)) - 1)
      when Col like '% %' then substring(Col, charindex(' ', Col) + 1, 30) else null end,
      Col3 = case when Col like '% % %' then right(Col, charindex(' ', reverse(Col)) - 1)
      else NULL end
    from @Table
    
    

    If your numbers are followed by a dash (-) and then alphabets

    You can continue like

     

    declare @Table table (Col varchar(30))
    
    insert @Table select '1235-A 1231-GD 234-F' union
    select '12343' union
    select '1213-F 343-E';
    
    with T as (
    	select Col1 = case when Col like '% %' then left(Col, charindex(' ', Col) - 1) else Col end,
    	  Col2 = case when Col like '% % %' then left(substring(Col, charindex(' ', Col) + 1, 30), charindex(' ', substring(Col, charindex(' ', Col) + 1, 30)) - 1)
    	  when Col like '% %' then substring(Col, charindex(' ', Col) + 1, 30) else null end,
    	  Col3 = case when Col like '% % %' then right(Col, charindex(' ', reverse(Col)) - 1)
    		else NULL end
    	from @Table
    )
    select Col1 = case when charindex('-', Col1) = 0 then Col1 else left(Col1, charindex('-', Col1) - 1) end,
      Col2 = case when charindex('-', Col2) = 0 then Col2 else left(Col2, charindex('-', Col2) - 1) end,
      Col3 = case when charindex('-', Col3) = 0 then Col3 else left(Col3, charindex('-', Col3) - 1) end
    from T
    
    

     

     

    • Marked as answer by KJian_ Thursday, April 21, 2011 7:15 AM
    Saturday, April 16, 2011 8:10 PM
  • Hi, if your data will be consistent in its format, then you can use the below tsql to get you expected output -

    DECLARE @Table TABLE (Col VARCHAR(30))
    
    INSERT INTO @Table
    SELECT '1235-A 1231-GD 234-F' UNION ALL
    SELECT '12343' UNION ALL
    SELECT '1213-F 343-E'
    
    SELECT
     Col,
     LEFT(Col + '-',CHARINDEX('-',Col + '-',1) -1) Col1,
     LEFT(SUBSTRING(Col,CHARINDEX(' ',Col + ' ',2),LEN(Col)) + '-',CHARINDEX('-',SUBSTRING(Col,CHARINDEX(' ',Col + ' ',2),LEN(Col)) + '-',1) -1) Col2,
     LEFT(SUBSTRING(Col,CHARINDEX(' ',Col + ' ',CHARINDEX(' ',Col + ' ',2) + 1),LEN(Col)) + '-',CHARINDEX('-',SUBSTRING(Col,CHARINDEX(' ',Col + ' ',CHARINDEX(' ',Col + ' ',2) + 1),LEN(Col)) + '-',1) -1) Col3
    FROM
     @Table
    


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    • Marked as answer by KJian_ Thursday, April 21, 2011 7:14 AM
    Sunday, April 17, 2011 9:56 AM

All replies

  • You need to first split your data into rows based on the space, then you can extract number only from each part using this blog post

    Extracting numbers with SQL Server

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by KJian_ Thursday, April 21, 2011 7:15 AM
    Friday, April 15, 2011 8:50 PM
  • The following block shows you how to separate the Column into three columns.

    If there are enough values for Col2 and Col3 then they are set as NULL.

     

    After that you can extract number as per the link given by Naomi.

     

    declare @Table table (Col varchar(30))
    
    insert @Table select '1235-A 1231-GD 234-F' union
    select '12343' union
    select '1213-F 343-E'
    
    select Col1 = case when Col like '% %' then left(Col, charindex(' ', Col) - 1) else Col end,
      Col2 = case when Col like '% % %' then left(substring(Col, charindex(' ', Col) + 1, 30), charindex(' ', substring(Col, charindex(' ', Col) + 1, 30)) - 1)
      when Col like '% %' then substring(Col, charindex(' ', Col) + 1, 30) else null end,
      Col3 = case when Col like '% % %' then right(Col, charindex(' ', reverse(Col)) - 1)
      else NULL end
    from @Table
    
    

    If your numbers are followed by a dash (-) and then alphabets

    You can continue like

     

    declare @Table table (Col varchar(30))
    
    insert @Table select '1235-A 1231-GD 234-F' union
    select '12343' union
    select '1213-F 343-E';
    
    with T as (
    	select Col1 = case when Col like '% %' then left(Col, charindex(' ', Col) - 1) else Col end,
    	  Col2 = case when Col like '% % %' then left(substring(Col, charindex(' ', Col) + 1, 30), charindex(' ', substring(Col, charindex(' ', Col) + 1, 30)) - 1)
    	  when Col like '% %' then substring(Col, charindex(' ', Col) + 1, 30) else null end,
    	  Col3 = case when Col like '% % %' then right(Col, charindex(' ', reverse(Col)) - 1)
    		else NULL end
    	from @Table
    )
    select Col1 = case when charindex('-', Col1) = 0 then Col1 else left(Col1, charindex('-', Col1) - 1) end,
      Col2 = case when charindex('-', Col2) = 0 then Col2 else left(Col2, charindex('-', Col2) - 1) end,
      Col3 = case when charindex('-', Col3) = 0 then Col3 else left(Col3, charindex('-', Col3) - 1) end
    from T
    
    

     

     

    • Marked as answer by KJian_ Thursday, April 21, 2011 7:15 AM
    Saturday, April 16, 2011 8:10 PM
  • I have code which split the values into rows you might rap some logic to get your output


    CREATE FUNCTION fn_SplitIn2Rows
        (
       @PInStrSource varchar(8000) = NULL,
          @pInChrSeparator char(1) = '-'
     )
     RETURNS
      @ARRAY TABLE (ItemValue VARCHAR(1000))
    AS
    BEGIN
     DECLARE @CurrentStr varchar(2000)
     DECLARE @ItemStr varchar(200)
     
     SET @CurrentStr = @PInStrSource
     
     WHILE Datalength(@CurrentStr) > 0
     BEGIN
      IF CHARINDEX(@pInChrSeparator, @CurrentStr,1) > 0
       BEGIN
                 SET @ItemStr = SUBSTRING (@CurrentStr, 1, CHARINDEX(@pInChrSeparator, @CurrentStr,1) - 1)
                 SET @CurrentStr = SUBSTRING (@CurrentStr, CHARINDEX(@pInChrSeparator, @CurrentStr,1) + 1, (Datalength(@CurrentStr) - CHARINDEX(@pInChrSeparator, @CurrentStr,1) + 1))
        INSERT @ARRAY (ItemValue) VALUES (@ItemStr)
          END
       ELSE
        BEGIN               
        INSERT @ARRAY (ItemValue) VALUES (@CurrentStr)    
                 BREAK;
              END
     END
     RETURN
    END

    Go

    --THE USAGE:
    SELECT * FROM Stores WHERE
      StoreId IN 
      (
       SELECT ItemValue FROM fn_SplitIn2Rows('1235-A 1231-GD 234-F' , '-')
      )


    http://uk.linkedin.com/in/ramjaddu
    Sunday, April 17, 2011 8:08 AM
  • Hi, if your data will be consistent in its format, then you can use the below tsql to get you expected output -

    DECLARE @Table TABLE (Col VARCHAR(30))
    
    INSERT INTO @Table
    SELECT '1235-A 1231-GD 234-F' UNION ALL
    SELECT '12343' UNION ALL
    SELECT '1213-F 343-E'
    
    SELECT
     Col,
     LEFT(Col + '-',CHARINDEX('-',Col + '-',1) -1) Col1,
     LEFT(SUBSTRING(Col,CHARINDEX(' ',Col + ' ',2),LEN(Col)) + '-',CHARINDEX('-',SUBSTRING(Col,CHARINDEX(' ',Col + ' ',2),LEN(Col)) + '-',1) -1) Col2,
     LEFT(SUBSTRING(Col,CHARINDEX(' ',Col + ' ',CHARINDEX(' ',Col + ' ',2) + 1),LEN(Col)) + '-',CHARINDEX('-',SUBSTRING(Col,CHARINDEX(' ',Col + ' ',CHARINDEX(' ',Col + ' ',2) + 1),LEN(Col)) + '-',1) -1) Col3
    FROM
     @Table
    


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    • Marked as answer by KJian_ Thursday, April 21, 2011 7:14 AM
    Sunday, April 17, 2011 9:56 AM