Post reply

replacing nulls

  • June 17, 2011 at 3:21 am

    #389760

    Hi there,

    I have concatenated a load of fields into one to then get distinct of these values however the problem I am having is that I have replaced both nulls and repeated entries with '' so my results look like this:

    data1 data2 data3 data4...

    data1 data2 data3 data4...

    data1 data2 data3 data4...

    When I do a distinct lookup on this I'm not getting rid of repeated data as the spacing is inconsistent.

    Does this make sense? Any ideas?

  • June 17, 2011 at 3:35 am

    #1340256

    Okay I did put uneven spacing but this text editor seems to have formatted it for me!

  • June 17, 2011 at 3:52 am

    #1340261

    you code and sample output would be useful , also since the editor formats the spaces u can try replaceing them with dots just so that we see what ur seeing.

    Jayanth Kurup[/url]

  • June 17, 2011 at 4:04 am

    #1340270

    Yeah, I'm at work so wasn't too sure about sensitive data etc.. I have attempted to desensitise it:

    select

    identity(int, 1,1) as row_no

    , col1

    , col2

    , col3

    into #1

    from table20

    where col1 between '2010' and '2011'

    and col5 = 'specific value'

    and col3 not in ('specific value','specific value','specific value')

    order by col1, col2

    -----------------------------

    select

    row_no - (select min(row_no) from #1 as S2 where S2.col1= S1.col1) + 1 as Row_no

    , col1

    , col2

    , col3

    into #2

    from #1 as S1

    ------------------------------

    select

    distinct col1

    , isnull((select col3 from #2 r1 where r1.col1 = #2.col1and row_no = 1), '')

    + isnull(case when (select col3 from #2 r2 where r2.col1 = #2.col1 and row_no = 2) = (select col3 from #2 r1 where r1.col1 = #2.col1 and row_no = 1) then '' else(select col3 from #2 r2 where r2.col1 = #2.col1 and row_no = 2) end, '')

    + isnull(case when (select col3 from #2 r3 where r3.col1 = #2.col1 and row_no = 3) = (select col3 from #2 r2 where r2.col1 = #2.col1 and row_no = 2) then '' else(select col3 from #2 r3 where r3.col1 = #2.col1 and row_no = 3) end, '')

    + isnull(case when (select col3 from #2 r4 where r4.col1 = #2.col1 and row_no = 4) = (select col3 from #2 r3 where r3.col1 = #2.col1 and row_no = 3) then '' else(select col3 from #2 r4 where r4.col1 = #2.col1 and row_no = 4) end, '')

    -- there are many more than this but you get the idea!

    as new_col

    into #new_table

    from #2

    -------------------

    select distinct new_col from #new_table order by new_col

  • June 17, 2011 at 4:16 am

    #1340282

    oh and replacing spaces with full stops:

    data1.......data2.data3.....data4...

    data1..data2.......data3..................data4...

    data1....data2..........data3..data4...

  • June 17, 2011 at 4:34 am

    #1340301

    Can I use charindex to do this?

  • June 17, 2011 at 6:14 am

    #1340345

    are the columns char or varchar datatype , also have you tried ltrim , rtrim on the results provided the by the case statements

    Jayanth Kurup[/url]

  • June 17, 2011 at 6:21 am

    #1340347

    try using replace(newcol, ' ','')

    Jayanth Kurup[/url]

  • June 17, 2011 at 8:28 am

    #1340472

    Right you are! It was copying the datatype of char (12) from the original table and that was causing the spaces :w00t:

    Cheers!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply