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

Last Reply 20 days ago By dharmendr

Posted 20 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

 

You are viewing reply posted by: dharmendr 20 days ago.
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