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

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