Calculate Sum of all and Odd Characters in a String in SQL Server

Last Reply 20 days ago By dharmendr

Posted 21 days ago

I have a value, which is 0100501, I need the following result in SQL-2012

Total Sum

0+1+0+0+5+0+1 = 7

Odd sum

0+0+5+1=6

 

Posted 20 days ago

Hi Mehram,

Check the below sample query. Use while loop to iterate each character and count the Total and the odd total.

SQL

DECLARE @test VARCHAR(20),@Counter INT,@Sum INT,@OddSum INT
SET @test = '0100501'
SET @Counter = 1
SET @Sum = 0
SET @OddSum = 0
WHILE (@Counter <= LEN(@test))
BEGIN
    SET @Sum = @Sum + SUBSTRING(@test,@Counter,1)
	IF(@Counter % 2 != 0)
	BEGIN
		SET @OddSum = @OddSum + SUBSTRING(@test,@Counter,1)
	END
    SET @Counter = @Counter + 1
END
SELECT @Sum 'Total Sum'
SELECT @OddSum 'Odd sum'

Output