none
Need to split a unconsistant column into different colmns

    Question

  • Hi,

    I have a column with space separated values , my task is to load it into different columns but the thing is they is no consistency in that columns .

    Column 

    500-02561
    500-07275 500-7275 500-A46846 500-G036872 500-AW8222589 500-1528131711

    500-100
    500-1000 500-A44431 500-G030457 500-AB8788448 500-1770594749

    Wanted the output as

    col1                     col2              col3                  col4                   col5                       col6

    500-02561

    500-07275    500-7275    500-A46846     500-G036872   500-AW8222589   500-1528131711

    500-100

    500-1000     500-A44431 500-G030457   500-AB8788448   500-1770594749.

    Tried using derived column 

    SUBSTRING(OTHER_NUMBERS_COL,1,(FINDSTRING(OTHER_NUMBERS_COL," ",1)) - 1)

    SUBSTRING(OTHER_NUMBERS_COL,(FINDSTRING(OTHER_NUMBERS_COL," ",1) + 1),(FINDSTRING(OTHER_NUMBERS_COL," ",2)) - (FINDSTRING(OTHER_NUMBERS_COL," ",1) + 1)).

    I would really appericate if some body could help me out with this :)

    Wednesday, July 24, 2013 11:19 PM

Answers

  • You need to split the values using some function like below and update the splitted values in col1 to col6 in sql server stored procedure.

    CREATE FUNCTION [dbo].[SDF_SplitString]
    (
        @sString nvarchar(2048),
        @cDelimiter nchar(1)
    )
    RETURNS @tParts TABLE ( part nvarchar(2048) )
    AS
    BEGIN
        if @sString is null return
        declare @iStart int,
        @iPos int
        if substring( @sString, 1, 1 ) = @cDelimiter 
        begin
        set @iStart = 2
        insert into @tParts
        values( null )
        end
        else 
        set @iStart = 1
        while 1=1
        begin
        set @iPos = charindex( @cDelimiter, @sString, @iStart )
        if @iPos = 0
        set @iPos = len( @sString )+1
        if @iPos - @iStart > 0
        insert into @tParts
        values ( substring( @sString, @iStart, @iPos-@iStart ))
        else
        insert into @tParts
        values( null )
        set @iStart = @iPos+1
        if @iStart > len( @sString ) 
        break
        end
        RETURN

    END


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

    Thursday, July 25, 2013 12:32 AM