Addition Of Digits

Question

Post reply

Addition Of Digits

Shadab Shah

SSCarpal Tunnel

Points: 4759
More actions
August 2, 2012 at 11:42 pm

#279220

Hi,
This was the question ask to one of my friend during an interview. He was ask to perform the addition of the digits.
Suppose the number is 985 the output would be 22(9+8+5).

Viewing 15 posts - 1 through 15 (of 37 total)

You must be logged in to reply to this topic. Login to reply

SomewhereSomehow SSCommitted Points: 1586 More actions · Answer 1

Here is my solution

declare @i int = 985;

with nums(n) as(select n from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10))nums(n))

select sum(convert(int,substring(convert(varchar(10),@i),n,1)))

from nums where n <= len(convert(varchar(10),@i))

I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
Blog: http://somewheresomehow.ru[/url]
Twitter: @SomewereSomehow

Shadab Shah SSCarpal Tunnel Points: 4759 More actions · Answer 2

Shadab Shah

SSCarpal Tunnel

Points: 4759

August 3, 2012 at 7:24 am

#1521376

Thanks that help 🙂

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

SomewhereSomehow (8/3/2012)
Here is my solution
declare @i int = 985;
with nums(n) as(select n from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10))nums(n))
select sum(convert(int,substring(convert(varchar(10),@i),n,1)))
from nums where n <= len(convert(varchar(10),@i))

This is good. A couple things to note:

First, the length of the input variable (@i) is limited to the size of the two varchar declarations. For example, say @i = 99999999999 (that's eleven 9's), you would get an overflow error when converting the expression to varchar...

No big deal; who cares?... Just change the varchars to varchar(20) or varchar(50) and declare @i as bigint. There. Problem solved!!!

Nope. Still have one thing to address and this WILL NOT produce an error[/b]. Instead you will just an incorrect aggregation. For the agregation to be accurate you would have to add additional values to your CTE. Again, say @i = 99999999999 (11 9's) you would return a 90(incorrect) instead of 99(correct). To fix this you would have to add an (11). If @i was 20 characters long you would have to add (11),(12)...(20).

A better way to write this would be:

DECLARE @i BIGINT=99999999999;

with nums(n) as

(

SELECT 1

UNION ALL

SELECT n+1 FROM nums WHERE n<30

)

select sum(convert(int,substring(convert(varchar(30),@i),n,1)))

from nums where n <= len(convert(varchar(30),@i));

Now @i can be 18 characters long (limited to 18 because of the bigint). How about we change @i to varchar(50). Now it works for a number that's 50 chars long.

Instead of:

select n from (values (1),(2),(3),(4)...(50))nums(n)

We are using some recursion:

with nums(n) as

(

SELECT 1

UNION ALL

SELECT n+1 FROM nums WHERE n<LEN(@i)

)

... and then we pull it all together:

DECLARE @i varchar(50)='99999999999999999999999999999999999999999999999999';

with nums(n) as

(

SELECT 1

UNION ALL

SELECT n+1 FROM nums WHERE n<LEN(@i)

)

select sum(convert(int,substring(convert(varchar(50),@i),n,1)))

from nums where n <= len(convert(varchar(50),@i));

GO

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

Here is my solution:

DECLARE @i varchar(50)='122333444455555';

WITH val(x,n) AS

(

SELECT LEFT(@i,LEN(@i)),'0'

UNION ALL

SELECT LEFT(x,LEN(x)-1),RIGHT(x,1) FROM val WHERE LEN(x)>0

)

SELECT SUM(CAST(n AS int)) FROM val

What's cool is you can replace SELECT SUM(CAST(n AS int)) FROM val

with SELECT * FROM val to see how it works.

Result set:

x n

-------------------------------------------------- ----

122333444455555 0

12233344445555 5

1223334444555 5

122333444455 5

12233344445 5

1223334444 5

122333444 4

12233344 4

1223334 4

122333 4

12233 3

1223 3

122 3

12 2

1 2

1

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

A little dynamic SQL with a string of 100 digits as input:

declare @val varchar(100) =

'20876543914578560430730723092317208765439145785604'+

'3073072309231720876543914578560430730723092317208'

declare @cmd varchar(300)

set @cmd = 'select [Sum] = '+

reverse(substring(reverse(replace(replace(replace(

replace(replace(replace(replace(replace(replace(replace(

convert(varchar(300),@val),'9','9+'),'8','8+'),'7','7+'),'6','6+')

,'5','5+'),'4','4+'),'3','3+'),'2','2+'),'1','1+'),'0','0+')),2,300))

print '@val = '+@val

print '@cmd = '+@cmd

exec (@cmd)

Results:

@val = 208765439145785604307307230923172087654391457856043073072309231720876543914578560430730723092317208

@cmd = select [Sum] = 2+0+8+7+6+5+4+3+9+1+4+5+7+8+5+6+0+4+3+0+7+3+0+7+2+3+0+9+2+3+1+7+2+0+8+7+6+5+4+3+9+1+4+5+7+8+5+6+0+4+3+0+7+3+0+7+2+3+0+9+2+3+1+7+2+0+8+7+6+5+4+3+9+1+4+5+7+8+5+6+0+4+3+0+7+3+0+7+2+3+0+9+2+3+1+7+2+0+8

Sum

-----------

403

(1 row(s) affected)

SomewhereSomehow SSCommitted Points: 1586 More actions · Answer 6

XMLSQLNinja,

Using the same logic. Yor solution will not work if there will be 51 digits.

Try to understand - my solution was intended to work only with int (and 999 999 999 99 is not int) in the same way, as yours only with 50 digits (btw, why 50, not 49 or 53?).

And one note, specifying input as string - not good idea imho, it coul be easily broken if there will be not a digit char in the string. If we talk about numbers, the input should be only one of numeric types - this is good form.

I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
Blog: http://somewheresomehow.ru[/url]
Twitter: @SomewereSomehow

SomewhereSomehow SSCommitted Points: 1586 More actions · Answer 7

CELKO,

Good idea, interesting approach! Smth tell's me that it would be also the fastest way of doing this!

I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
Blog: http://somewheresomehow.ru[/url]
Twitter: @SomewereSomehow

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 8

Another way, up to BIGINTs only

DECLARE @num BIGINT = 985;

WITH Tens(Pos,Val) AS (

SELECT 1, CAST(1 AS BIGINT) UNION ALL

SELECT 2, CAST(10 AS BIGINT) UNION ALL

SELECT 3, CAST(100 AS BIGINT) UNION ALL

SELECT 4, CAST(1000 AS BIGINT) UNION ALL

SELECT 5, CAST(10000 AS BIGINT) UNION ALL

SELECT 6, CAST(100000 AS BIGINT) UNION ALL

SELECT 7, CAST(1000000 AS BIGINT) UNION ALL

SELECT 8, CAST(10000000 AS BIGINT) UNION ALL

SELECT 9, CAST(100000000 AS BIGINT) UNION ALL

SELECT 10,CAST(1000000000 AS BIGINT) UNION ALL

SELECT 11,CAST(10000000000 AS BIGINT) UNION ALL

SELECT 12,CAST(100000000000 AS BIGINT) UNION ALL

SELECT 13,CAST(1000000000000 AS BIGINT) UNION ALL

SELECT 14,CAST(10000000000000 AS BIGINT) UNION ALL

SELECT 15,CAST(100000000000000 AS BIGINT) UNION ALL

SELECT 16,CAST(1000000000000000 AS BIGINT) UNION ALL

SELECT 17,CAST(10000000000000000 AS BIGINT) UNION ALL

SELECT 18,CAST(100000000000000000 AS BIGINT) UNION ALL

SELECT 19,CAST(1000000000000000000 AS BIGINT))

SELECT SUM((@num / Val) % 10)

FROM Tens

WHERE Val<=@num;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 9

another method here:

http://sqlbump.blogspot.co.uk/2010/06/sum-of-digits-of-number.html

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

SomewhereSomehow SSCommitted Points: 1586 More actions · Answer 10

Cool!!! 4 ways to solve the problem! Definitly all go to my KB.

CELKO's - is the fastest or isn't it? Who will make a proof test?

ps

CELKO,

Are you a Joe Celko, the one, who was the author of marvellous chapter in Itzik Ben Gan book? (Sorry for tactless question).

I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
Blog: http://somewheresomehow.ru[/url]
Twitter: @SomewereSomehow

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 11

SomewhereSomehow (8/5/2012)
...Who will make a proof test?

go on...give it a crack:-)

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

SQL Padawan Right there with Babe Points: 792 More actions · Answer 12

I could be wrong but Celko's method is the fastest. One thing to keep in mind is the LEN function's limit is 8000 characters or in other words the sum of all digits can not exceed 8000...

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 13

Million row test for three of the solutions. Results are interesting/surpising. Perhaps some of you folks could check this and run it.

--===== Conditionally drop the test table to make reruns in SSMS easier.

-- This is NOT a part of the solution.

IF OBJECT_ID('tempdb..#Test','U') IS NOT NULL

DROP TABLE #Test

;

--===== Create and populate the test table.

-- This is NOT a part of the solution.

SELECT TOP (1000000)

TestID = IDENTITY(INT,1,1),

TestNumber = ABS(CHECKSUM(NEWID()))

INTO #Test

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

;

--===== Add the expected PK

ALTER TABLE #Test

ADD PRIMARY KEY CLUSTERED (TestID)

;

-----------------------------------------------------------------------------

PRINT '========== SomewhereSomehow ==========================================='

SET STATISTICS TIME ON;

DECLARE @T INT;

with nums(n) as(select n from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10))nums(n))

select @T = sum(convert(int,substring(convert(varchar(10),TestNumber),n,1)))

from nums

CROSS JOIN #Test

where n <= len(convert(varchar(10),TestNumber))

GROUP BY TestNumber;

SET STATISTICS TIME OFF;

GO

PRINT '========== CELKO ==========================================='

SET STATISTICS TIME ON;

DECLARE @T INT

SELECT @T = LEN ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (CAST (TestNumber AS VARCHAR(150)) ,'0', '') ,'1', '#')

,'2', '##') ,'3', '###') ,'4', '####') ,'5', '#####') ,'6', '######') ,'7', '#######') ,'8', '########') ,'9', '#########'))

FROM #Test;

SET STATISTICS TIME OFF;

GO

PRINT '========== Mark ====================================================='

SET STATISTICS TIME ON;

DECLARE @T INT;

WITH Tens(Pos,Val) AS (

SELECT 1, 1 UNION ALL

SELECT 2, 10 UNION ALL

SELECT 3, 100 UNION ALL

SELECT 4, 1000 UNION ALL

SELECT 5, 10000 UNION ALL

SELECT 6, 100000 UNION ALL

SELECT 7, 1000000 UNION ALL

SELECT 8, 10000000 UNION ALL

SELECT 9, 100000000 UNION ALL

SELECT 10,1000000000)

SELECT @T = SUM((TestNumber / Val) % 10)

FROM Tens

CROSS JOIN #Test

WHERE Val<=TestNumber

GROUP BY TestNumber;

SET STATISTICS TIME OFF;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Cadavre SSC-Forever Points: 41690 More actions · Answer 14

Mark-101232 (8/6/2012)
Million row test for three of the solutions. Results are interesting/surpising. Perhaps some of you folks could check this and run it.

Celko forgot to account for the REPLACE "bug".

Change his code to this: -

DECLARE @T INT = 985;

SELECT LEN ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (CAST (@T AS VARCHAR(150)) COLLATE Latin1_General_BIN2,'0', '') ,'1', '#')

,'2', '##') ,'3', '###') ,'4', '####') ,'5', '#####') ,'6', '######') ,'7', '#######') ,'8', '########') ,'9', '#########'));

Then take another look at your test results: -

--===== Conditionally drop the test table to make reruns in SSMS easier.

-- This is NOT a part of the solution.

IF OBJECT_ID('tempdb..#Test','U') IS NOT NULL

DROP TABLE #Test

;

--===== Create and populate the test table.

-- This is NOT a part of the solution.

SELECT TOP (1000000)

TestID = IDENTITY(INT,1,1),

TestNumber = ABS(CHECKSUM(NEWID()))

INTO #Test

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

;

--===== Add the expected PK

ALTER TABLE #Test

ADD PRIMARY KEY CLUSTERED (TestID)

;

-----------------------------------------------------------------------------

PRINT '========== SomewhereSomehow ==========================================='

SET STATISTICS TIME ON;

DECLARE @T INT;

with nums(n) as(select n from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10))nums(n))

select @T = sum(convert(int,substring(convert(varchar(10),TestNumber),n,1)))

from nums

CROSS JOIN #Test

where n <= len(convert(varchar(10),TestNumber))

GROUP BY TestNumber;

SET STATISTICS TIME OFF;

GO

PRINT '========== CELKO ==========================================='

SET STATISTICS TIME ON;

DECLARE @T INT

SELECT @T = LEN ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (CAST (TestNumber AS VARCHAR(150)) ,'0', '') ,'1', '#')

,'2', '##') ,'3', '###') ,'4', '####') ,'5', '#####') ,'6', '######') ,'7', '#######') ,'8', '########') ,'9', '#########'))

FROM #Test;

SET STATISTICS TIME OFF;

GO

PRINT '========== Mark ====================================================='

SET STATISTICS TIME ON;

DECLARE @T INT;

WITH Tens(Pos,Val) AS (

SELECT 1, 1 UNION ALL

SELECT 2, 10 UNION ALL

SELECT 3, 100 UNION ALL

SELECT 4, 1000 UNION ALL

SELECT 5, 10000 UNION ALL

SELECT 6, 100000 UNION ALL

SELECT 7, 1000000 UNION ALL

SELECT 8, 10000000 UNION ALL

SELECT 9, 100000000 UNION ALL

SELECT 10,1000000000)

SELECT @T = SUM((TestNumber / Val) % 10)

FROM Tens

CROSS JOIN #Test

WHERE Val<=TestNumber

GROUP BY TestNumber;

SET STATISTICS TIME OFF;

GO

PRINT '========== IMPROVED CELKO ==========================================='

SET STATISTICS TIME ON;

DECLARE @T INT;

SELECT @T = LEN ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (CAST (TestNumber AS VARCHAR(150)) COLLATE Latin1_General_BIN2,'0', '') ,'1', '#')

,'2', '##') ,'3', '###') ,'4', '####') ,'5', '#####') ,'6', '######') ,'7', '#######') ,'8', '########') ,'9', '#########'))

FROM #Test;

SET STATISTICS TIME OFF;

GO

========== SomewhereSomehow ===========================================

SQL Server Execution Times:

CPU time = 28155 ms, elapsed time = 8756 ms.

========== CELKO ===========================================

SQL Server Execution Times:

CPU time = 24343 ms, elapsed time = 24385 ms.

========== Mark =====================================================

SQL Server Execution Times:

CPU time = 19921 ms, elapsed time = 6877 ms.

========== IMPROVED CELKO ===========================================

SQL Server Execution Times:

CPU time = 3594 ms, elapsed time = 3619 ms.

--edit--

Found the link to the replace "bug"

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/