none
how to calculate odd and even place digits in SQL? RRS feed

  • Question

  • Hi,

    I have a field called number-196421744643

    now i want to pick odd and even place numbers. For example odd place 1+6+2+7+4+4=24 and even place number 9+4+1+4+6+3=27

    I need this to be calculated for all the rows innumber field. Do we have any in buit function is provided or we need to calculate by using some logic? please help me on this.

    Thanks,

    Jagadeesh

    Wednesday, April 16, 2014 3:27 PM

Answers

  • Please see the e.g bellow

    
    -- =============================================
    CREATE FUNCTION fn_r(@num bigint)

    RETURNS TABLE 
    AS
    RETURN 
    (
    with rs
    as
    (
    select 1 pl,substring(CAST(@num as varchar(20)),1,1) n
    union all
    select pl+1,substring(CAST(@num as varchar(20)),pl,1)  from rs where pl<=datalength(CAST(@num as varchar(20)))

    )
    ,rs1
    as(
    select pl-1 pl,n from rs where pl>1
    )
    select sum(case  when (pl % 2)= 1 then n else 0 end)odd, sum(case  when (pl % 2)= 0 then n else 0 end)even  from rs1


    )
    GO

    select *
     from (values(196421744643),(342342342))rs(n)
     cross apply fn_r(rs.n)t

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    Wednesday, April 16, 2014 3:59 PM
  • create table test (number varchar(50))
    Insert into test values('196421744643'),('12121212121212')
    select number,  SUM(Case WHEN num%2 =1 then Cast(substring(number,num,1) as int) end) OddSum,
    SUM(Case WHEN num%2 =0 then Cast(substring(number,num,1) as int) end) EvenSum
     from test
     cross apply (Values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)) d(num) 
     WHERE substring(number,num,1)<>''
     Group by number
     
     drop table test

    Wednesday, April 16, 2014 5:11 PM
    Moderator

All replies

  • Please see the e.g bellow

    
    -- =============================================
    CREATE FUNCTION fn_r(@num bigint)

    RETURNS TABLE 
    AS
    RETURN 
    (
    with rs
    as
    (
    select 1 pl,substring(CAST(@num as varchar(20)),1,1) n
    union all
    select pl+1,substring(CAST(@num as varchar(20)),pl,1)  from rs where pl<=datalength(CAST(@num as varchar(20)))

    )
    ,rs1
    as(
    select pl-1 pl,n from rs where pl>1
    )
    select sum(case  when (pl % 2)= 1 then n else 0 end)odd, sum(case  when (pl % 2)= 0 then n else 0 end)even  from rs1


    )
    GO

    select *
     from (values(196421744643),(342342342))rs(n)
     cross apply fn_r(rs.n)t

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    Wednesday, April 16, 2014 3:59 PM
  • Try this,

    alter function getSum(@value bigint,@type varchar(10)) returns int begin declare @sumO int,@sumE int,@temp int declare @t table (col2 nvarchar(50)) insert into @t values(@value) ----------------------- query ;with cte as ( select substring(col2, 1, 1) as Chars,stuff(col2, 1, 1, '') as col2, 1 as RowID from @t union all select substring(col2, 1, 1) as Chars,stuff(col2, 1, 1, '') as col2, RowID + 1 as RowID from cte where len(col2) > 0 ) select @sumO=(select sum(cast(chars as int)) from cte where rowid % 2 <> 0), @sumE=(select sum(cast(chars as int)) from cte where rowid % 2 = 0) if @type='Odd' set @temp = @sumO else if @type='Even' set @temp=@sumE return @temp end go select dbo.getSum('196421744643','Even') EvenSum

    select dbo.getSum('196421744643','Odd') OddSum



    Regards, RSingh

    Wednesday, April 16, 2014 5:09 PM
  • create table test (number varchar(50))
    Insert into test values('196421744643'),('12121212121212')
    select number,  SUM(Case WHEN num%2 =1 then Cast(substring(number,num,1) as int) end) OddSum,
    SUM(Case WHEN num%2 =0 then Cast(substring(number,num,1) as int) end) EvenSum
     from test
     cross apply (Values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)) d(num) 
     WHERE substring(number,num,1)<>''
     Group by number
     
     drop table test

    Wednesday, April 16, 2014 5:11 PM
    Moderator
  • For numbers, it would be unusual to start with the most significant digit and move towards the least significant.  OTOH that is common for strings.  Be careful how you interpret your requirements.
    Wednesday, April 16, 2014 5:37 PM
  • Thank you all for the replies.

    I dont want to create a function for this.I need to achieve this by a query(using some built -in function)

    Do we have any command which takes the odd and even position digits and sums it?

    Thanks

    Jkumar

    Thursday, April 17, 2014 1:27 PM
  • If you use varchar(50), then why do you get only 15 first digits? I suspect you need to use varchar(15) as your number then to the maximum of 15 digits per number.

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


    My blog


    My TechNet articles

    Thursday, April 17, 2014 6:37 PM
    Moderator
  • Thank you all for the replies.

    I dont want to create a function for this.I need to achieve this by a query(using some built -in function)

    Do we have any command which takes the odd and even position digits and sums it?

    Thanks

    Jkumar

    No there isn't any

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Thursday, April 17, 2014 7:12 PM