Madhivanan's TSQL Blog

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Blogs

Upload Image

Close it

Select File


	Like

Invite Friends

Browse by Tags · View All

sql_server	217
t-sql	211
tsql	116
sqlserver	96
BRH	78
#SQLServer	66
#TSQL	56
SQL Server	34
function	11
SSMS	9

Archive · View All

August 2007	17
August 2010	8
June 2012	7
June 2011	7
November 2007	7
August 2012	6
May 2012	6
November 2011	6
August 2011	6
October 2011	6

Home · Posts · Most Liked · Most Discussed · Ask a Question · Fans (385) · Preferences

Sum up digits of a number

May 4 2012 12:00AM by Madhivanan

My friend told me that in an interview he was asked to write a code that sums the digits of the number. He asked me if there is a way of doing it without using a WHILE loop. Here are two methods

Method 1 : Dynamic SQL

declare @i int, @sql varchar(8000)
set @i=798273
set @sql=''
select 
	@sql=@sql+substring(ltrim(@i),number,1)+'+' 
from 
	master..spt_values 
where 
	type='p' and number between 1 and len(@i)
select @sql='select '+left(@sql,len(@sql)-1)

exec(@sql+' as sum_of_digits')

Method 2 : Static SQL

declare @i int, @n int
set @i=798273
set @n=0
select 
	@n=@n+substring(ltrim(@i),number,1) 
from 
	master..spt_values 
where 
	type='p' and number between 1 and len(@i)

select @n as sum_of_digits

Method 3 : Without using any variables

declare @i int
set @i=798273
select 
	sum(substring(ltrim(@i),number,1)*1)
from 
	master..spt_values 
where 
	type='p' and number between 1 and len(@i)

All the methods would return the following result

sum_of_digits
-------------
36

Here master..spt_values is a system table which is used as a tally number table. You can use your own table too.

Tags:

Previous: Temporary table and dynamic sql
Next: Question of the month May 2012 - How does direct usage of column alias work in ORDER BY clause?

Madhivanan

3 · 39% · 12429

4 Readers said this post helped them to refresh memory

	Dave Vroman said this post helped them to refresh memory on 5/4/2012 2:34:00 PM Profile
	jcelko said this post helped them to refresh memory on 8/2/2012 2:41:00 PM Profile · Facebook
	waelguirguis said this post helped them to refresh memory on 8/5/2012 8:04:00 AM Profile · Twitter
	Divya Agrawal Shah said this post helped them to refresh memory on 10/18/2012 4:47:00 AM Profile · Blog · Facebook · Twitter

Liked

Lifesaver

Refreshed

Learned

Incorrect


Like this
Post this to my facebook wall
Tweet this
This was a true life saver
Thanks, this helped me to refresh my memory
Very informative, I just learned something new
I disagree, this information is incorrect


	Submit

43 Comments

Method 4 : Using xml (without variables)

select [sum_of_digits]=
       cast(cast(cast(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(@i ,'0', '0')
                       ,'1', '1')
                       ,'2', '2')
                       ,'3', '3')
                       ,'4', '4')
                       ,'5', '5')
                       ,'6', '6')
                       ,'7', '7')
                       ,'8', '8')
                       ,'9', '9')
                       as xml).query('sum(/a)') as varchar) as int)

regards Zbigniew

commented on May 7 2012 2:18AM

Zibi

25 · 6% · 2029

nice Zibi

commented on May 7 2012 3:08AM

Nirav

37 · 5% · 1593

Good to know Zibi. Thanks for sharing...

commented on May 7 2012 3:52AM

Hardik Doshi

20 · 9% · 2839

Zibi, thanks for the alternate method. But it throws the following error

Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value '0.0E0' to data type int.

commented on May 7 2012 4:29AM

Madhivanan

3 · 39% · 12429

Hello, this below is proof of such errors

select [sum_of_digits]=
       cast(replace(cast(cast(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(@i ,'0', '0')
                       ,'1', '1')
                       ,'2', '2')
                       ,'3', '3')
                       ,'4', '4')
                       ,'5', '5')
                       ,'6', '6')
                       ,'7', '7')
                       ,'8', '8')
                       ,'9', '9')
                       as xml).query('sum(/a)') as varchar), '0.0E0', '0') as int)

commented on May 7 2012 4:38AM

Zibi

25 · 6% · 2029

As XML parsing is case sensitive, please find below modified query:

declare @i int, @sql varchar(8000) 
set @i=798273 
select [sum_of_digits]=
       cast(replace(cast(cast(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(@i ,'0', '<A>0</A>')
                       ,'1', '<A>1</A>')
                       ,'2', '<A>2</A>')
                       ,'3', '<A>3</A>')
                       ,'4', '<A>4</A>')
                       ,'5', '<A>5</A>')
                       ,'6', '<A>6</A>')
                       ,'7', '<A>7</A>')
                       ,'8', '<A>8</A>')
                       ,'9', '<A>9</A>')
                       as xml).query('sum(/A)') as varchar), '0.0E0', '0') as int)

commented on May 7 2012 4:45AM

Hardik Doshi

20 · 9% · 2839

Ok. It just seems to be case sensitive. You dont need to replace 0.0E0. This is enough

declare @i int
set @i=798273 
select [sum_of_digits]=
       cast(cast(cast(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(
            replace(@i ,'0', '<A>0</A>')
                       ,'1', '<A>1</A>')
                       ,'2', '<A>2</A>')
                       ,'3', '<A>3</A>')
                       ,'4', '<A>4</A>')
                       ,'5', '<A>5</A>')
                       ,'6', '<A>6</A>')
                       ,'7', '<A>7</A>')
                       ,'8', '<A>8</A>')
                       ,'9', '<A>9</A>')
                       as xml).query('sum(/A)') as varchar) as int)

commented on May 7 2012 4:50AM

Madhivanan

3 · 39% · 12429

Here's another way to sum the digits of a 32-bit signed integer.

CREATE FUNCTION SumOfDigits(@N int)
RETURNS int AS BEGIN RETURN
(
    SELECT ABS(@N) % 10
    + ABS(@N) / CAST(1E1 AS int) % 10
    + ABS(@N) / CAST(1E2 AS int) % 10
    + ABS(@N) / CAST(1E3 AS int) % 10
    + ABS(@N) / CAST(1E4 AS int) % 10
    + ABS(@N) / CAST(1E5 AS int) % 10
    + ABS(@N) / CAST(1E6 AS int) % 10
    + ABS(@N) / CAST(1E7 AS int) % 10
    + ABS(@N) / CAST(1E8 AS int) % 10
    + ABS(@N) / CAST(1E9 AS int) % 10
)
END

commented on May 7 2012 5:14AM

Anthony Faull

1217 · 0% · 20

Good one tonyfaull

commented on May 7 2012 5:52AM

Madhivanan

3 · 39% · 12429

Hello, another xml approuch regards Zbigniew

declare @i int, @sql varchar(8000)
set @i=798273

select [sum_of_digits]=cast(
                        cast( 
                              cast(''+cast(@i as varchar)+'' as xml).query(
                                           'for $i in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13) 
                                              return 
                                                       {fn:substring((/a[1])[1], $i, 1)}
                                                     
                                           ')
                                 .query('xs:int(sum(/a))') as varchar) as int
                            )
go

commented on May 7 2012 8:26AM

Zibi

25 · 6% · 2029

Zibi, thats interesting one. However take care of case sensitive issue. Here is corrected code

declare @i int, @sql varchar(8000)
set @i=798273

select [sum_of_digits]=cast(
                        cast( 
                              cast('<A>'+cast(@i as varchar)+'</A>' as xml).query(
                                           'for $i in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13) 
                                              return <A>
                                                       {fn:substring((/A[1])[1], $i, 1)}
                                                     </A>
                                           ')
                                 .query('xs:int(sum(/A))') as varchar) as int
                            )
go

commented on May 7 2012 8:45AM

Madhivanan

3 · 39% · 12429

select len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(@i, '0',''),'1','a'),'2','aa'),'3','aaa'),'4','aaaa'),'5','aaaaa'),'6','aaaaaa'),'7','aaaaaaa'),'8','aaaaaaaa'),'9','aaaaaaaaa'))

commented on May 7 2012 10:39AM

Leszek Gniadkowski

8 · 18% · 5718

Hello, next approuch regards Zbigniew

select [sum_of_digits]=cast(cast(
                                     cast(replicate('',len(@i))+''+cast(@i as varchar)+'' as xml).query(
                                          'for $i in /b
                                            let $cnt := 1+count($i/../*[. << $i])
                                            return 
                                                      {fn:substring((/a[1])[1], $cnt, 1)}
                                                   
                                         ')
                                    .query('xs:int(sum(/a))') as varchar) as int)

commented on May 7 2012 11:10AM

Zibi

25 · 6% · 2029

Hi,

Another approach using LEFT AND RIGHT ,

                                select SUM( CAST (LEFT(RIGHT(@i,LEN(@i)-(number-1)),1) AS INt))
                                                   from master..spt_values
                                                   where number between 1 and LEN(@i)
                                                             and type='p'

commented on Jul 31 2012 5:14AM

Sarat

530 · 0% · 69

Good one Madhi.

commented on Jul 31 2012 1:47PM

Roji Thomas

791 · 0% · 36

Thanks Roji. I have not seen you very active in online forums. Are you busy with your work?

commented on Aug 1 2012 1:48AM

Madhivanan

3 · 39% · 12429

This is good problem! I have had to do this with weights to compute a check digit, so the position of digit in the string was important. But we do not have to worry about that with just a simple sum. We can just convert each digit into a hash mark. Concatenate them and measure the length. This is pure ANSI/ISO Standard SQL without any XML or other foreign language contamenation.

SET @digitssum = LEN ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (CAST (@ininteger AS VARCHAR(15)) ,'0', '') ,'1', '#') ,'2', '##') ,'3', '###') ,'4', '####') ,'5', '#####') ,'6', '######') ,'7', '#######') ,'8', '########') ,'9', '#########'));

commented on Aug 2 2012 2:37PM

jcelko

444 · 0% · 87

Typo: CAST (@in_integer AS VARCHAR(150))

commented on Aug 2 2012 2:43PM

jcelko

444 · 0% · 87

Thanks Madhi. Trying to be bit more active these days.

I'll also offer couple of solutions.

DECLARE @i INT = 987654321
DECLARE @c VARCHAR(250) = CONVERT(VARCHAR, @i)
SELECT @c = REPLACE(@c,n,REPLICATE('*',n))
FROM (SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY @@SPID) -1 FROM sys.objects) T(n)
SELECT LEN(@c)

go
DECLARE @i INT = 123456789
DECLARE @c INT = 0
SELECT @c = @c +  ((@i /n)%10)  
FROM (SELECT TOP 10 POWER(10,(ROW_NUMBER() 
    	OVER(ORDER BY @@SPID))-1) FROM sys.objects) T(n)
WHERE n <= @i
SELECT @C

commented on Aug 2 2012 5:56PM

Roji Thomas

791 · 0% · 36

Thanks. Joe celko. That's interesting. I have seen you using it for simulating INITCAP function

commented on Aug 3 2012 3:07AM

Madhivanan

Madhivanan's TSQL Blog

Sum up digits of a number

43 Comments

Your Comment