Counting numbers from range

  • Hi

    I have a table which has column that contains data in range and csv format.

    like

    table1

    col1 col2 col3..........

    a 6-10,4,5 hjkj..............

    b 0 81 uiu....

    c 5-16,3,6,50 jhlkjlk........

    I want to get the greatest of the number from this string.

    say in col1 greatest no is 10 , in col2 its 81, in col3 it is 50

    how to get this greatest no

  • Hi Khushbu,

    Below is my answer to your problem, you should create a function, then you can make a max.

    alter function dbo.token_char

    (

    @col1char(1),

    @col2varchar(200)

    )

    RETURNS @tokens table (

    col1 char(1),

    col2 varchar(200)

    )

    as

    begin

    declare @var varchar(200)

    set @var=replace(replace(replace(@col2,' ','.'),'-','.'),',','.')

    while len(@var)>0

    begin

    if CharIndex('.',@var)<>0

    begin

    insert into @tokens (col1,col2)

    values(@col1, substring(@var,1,CharIndex('.',@var)-1))

    set @var=substring(@var,CharIndex('.',@var)+1,len(@var))

    end

    else

    begin

    insert into @tokens (col1,col2)

    values(@col1, @var)

    set @var=''

    end

    end

    return

    end

    select b.col1,max(cast(b.col2 as int))

    from (

    select 'a' as col1, '6-10,4,5' as col2, 'hjkj....' as col3 union all

    select 'b' as col1, '0 81' as col2, 'uiu....' as col3 union all

    select 'c' as col1, '5-16,3,6,50' as col2, 'jhlkjlk....' as col3

    ) a

    cross apply dbo.token_char(a.col1,a.col2) b

    group by b.col1

    Thanks,

    Jorge.

Viewing 2 posts - 1 through 1 (of 1 total)

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