Addition Of Digits

  • 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).

  • 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

  • Thanks that help 🙂

  • 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

  • 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

  • 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)

  • 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

  • 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

  • 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
  • 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

  • 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

  • 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

  • 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...

  • 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
  • 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/

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

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