locked
need some idea how to do it RRS feed

  • Question

  • Hi,

    i have column in the source database having values like:

    1

    2-1

    3-1-1

    4-2-3

    10-2-5

    11-12-2

    like this.

    i want to get this data into my data warehouse table like below.

    1  

    2    

    3    

    4     

    10  

    12

    but not getting any idea how to do it.

    any help?

    Thanks in advance.

    Monday, August 27, 2012 1:52 PM

Answers

  • You need to explain the rules; to me based on the pattern it looks like the results should instead be:

    1  
    2    
    3    
    4     
    10  
    11

    with the 11 instead of 12.  Here is an example for a starting idea:

    declare @test table (dirty_String varchar(9));
    insert into @test
    select '1' union all
    select '2-1' union all
    select '3-1-1' union all
    select '4-2-3' union all
    select '10-2-5' union all
    select '11-12-2'
    ;
    select
      dirty_String,
    --  sub_Length,
      left(dirty_String, coalesce(sub_Length, len(dirty_String))
      ) as results
    from @test
    cross apply
    ( select nullif(charindex('-', dirty_String), 0) -1 as sub_Length 
    ) xa1;
    /* -------- Output: --------
    dirty_String results
    ------------ ---------
    1            1
    2-1          2
    3-1-1        3
    4-2-3        4
    10-2-5       10
    11-12-2      11
    */

    • Edited by Kent Waldrop Monday, August 27, 2012 2:33 PM
    • Proposed as answer by amber zhang Tuesday, August 28, 2012 6:30 AM
    • Marked as answer by Maggie Luo Wednesday, September 5, 2012 11:49 AM
    Monday, August 27, 2012 2:24 PM