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


Upload Image Close it
Select File

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

Madhivanan's TSQL Blog

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: 


Madhivanan
3 · 40% · 12899
16
 
 
 
 
0
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% · 2030
  • 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% · 2853
  • 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 · 40% · 12899
  • 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% · 2030
  • 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% · 2853
  • 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 · 40% · 12899
  • 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
    1249 · 0% · 20
  • Good one tonyfaull

    commented on May 7 2012 5:52AM
    Madhivanan
    3 · 40% · 12899
  • 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% · 2030
  • 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 · 40% · 12899
  • 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% · 5729
  • 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% · 2030
  • 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
    506 · 0% · 74
  • Good one Madhi.

    commented on Jul 31 2012 1:47PM
    Roji Thomas
    803 · 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 · 40% · 12899
  • 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
    453 · 0% · 87
  • Typo: CAST (@in_integer AS VARCHAR(150))

    commented on Aug 2 2012 2:43PM
    jcelko
    453 · 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
    803 · 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
    3 · 40% · 12899
Previous 1 | 2 | 3 Next

Your Comment


Sign Up or Login to post a comment.

"Sum up digits of a number" rated 5 out of 5 by 16 readers
Sum up digits of a number , 5.0 out of 5 based on 16 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]