Looping through string, adding all numbers e.g. '123' = 1+2+3, demo with working loop included

Question

This works to output the string 123456 as:

1
2
3
4
5
6

Code:

declare @string varchar(20)
declare @index int
declare @len int
declare @char char(1)

set @string = '123456'
set @index = 1
set @len= LEN(@string)

WHILE @index<= @len
BEGIN
set @char = SUBSTRING(@string, @index, 1)
print @char

SET @index= @index+ 1
END

But when I try to use this code to add those values up when looping through them (1+2+3+4+5+6 = 21), then I do no get a result from SQL, please help

declare @string varchar(20)
declare @index int
declare @len int
declare @char char(1)

set @string = '123456'
set @index = 1
set @len= LEN(@string)

declare @Total int 


WHILE @index<= @len
BEGIN
set @char = SUBSTRING(@string, @index, 1)
set @Total = @Total + cast(@char as int)

SET @index= @index+ 1
END

print @Total

Answer 1

You need to set @Total to a default value, otherwise it is NULL and NULL + anything always returns NULL.

Changing this line returns 21 for me:

declare @Total int

to

declare @Total int = 0

That being said, this is not a great thing to try to do on a large number of rows. If you need to do this on scalar values like you are using there, you may want to consider using a scalar udf for it. This will NOT scale well though so if you use it against a table be prepared to wait a while.