# how to calculate odd and even place digits in SQL? • ### 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,

Wednesday, April 16, 2014 3:27 PM

• -- =============================================
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

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

### All replies

• -- =============================================
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

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') EvenSumselect 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
• 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
• 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