sum of digits in sql

Question

The database scheme consists of four tables:

Product(maker, model, type)    
PC(code, model, speed, ram, hd, cd, price)    
Laptop(code, model, speed, ram, hd, screen, price)    
Printer(code, model, color, type, price)

• The Product table contains data on the maker, model number, and type of product ('PC', 'Laptop', or 'Printer'). It is assumed that model numbers in the Product table are unique for all makers and product types.

• Each personal computer in the PC table is unambiguously identified by a unique code, and is additionally characterized by its model (foreign key referring to the Product table), processor speed (in MHz) – speed field, RAM capacity (in Mb) - ram, hard disk drive capacity (in Gb) – hd, CD-ROM speed (e.g, '4x') - cd, and its price.

• The Laptop table is similar to the PC table, except that instead of the CD-ROM speed, it contains the screen size (in inches) – screen.
• For each printer model in the Printer table, its output type (‘y’ for color and ‘n’ for monochrome) – color field, printing technology ('Laser', 'Jet', or 'Matrix') – type, and price are specified.

Calculate the sum of digits in each model's ID (model column) from Product table. Result set: model, sum of digits

Please tell me how to solve it. I am of intermediate sql skill and cant solve this.

Answer 1

It's quite interesting, that my solution with LEN method hasn't passes the check. The only difference is the method.

According to documentation the main difference between LEN and DATALENGTH is that DATALENGTH counts bytes, LEN counts symbols.

So it looks like that LEN method is better for the task. But stiil we can pass chech only with DATALENGTH .

SELECT 
    model, 
    1 * (len(model) - len(replace(model,'1',''))) +
    2 * (len(model) - len(replace(model,'2',''))) +
    3 * (len(model) - len(replace(model,'3',''))) +
    4 * (len(model) - len(replace(model,'4',''))) +
    5 * (len(model) - len(replace(model,'5',''))) +
    6 * (len(model) - len(replace(model,'6',''))) +
    7 * (len(model) - len(replace(model,'7',''))) +
    8 * (len(model) - len(replace(model,'8',''))) +
    9 * (len(model) - len(replace(model,'9','')))  AS 'sum_fig'
FROM product

Answer 2

This solution passes the check

SELECT model, 
    1 * (DATALENGTH(model) - DATALENGTH(REPLACE(model, '1', ''))) +
    2 * (DATALENGTH(model) - DATALENGTH(REPLACE(model, '2', ''))) +
    3 * (DATALENGTH(model) - DATALENGTH(REPLACE(model, '3', ''))) +
    4 * (DATALENGTH(model) - DATALENGTH(REPLACE(model, '4', ''))) +
    5 * (DATALENGTH(model) - DATALENGTH(REPLACE(model, '5', ''))) +
    6 * (DATALENGTH(model) - DATALENGTH(REPLACE(model, '6', ''))) +
    7 * (DATALENGTH(model) - DATALENGTH(REPLACE(model, '7', ''))) +
    8 * (DATALENGTH(model) - DATALENGTH(REPLACE(model, '8', ''))) +
    9 * (DATALENGTH(model) - DATALENGTH(REPLACE(model, '9', ''))) AS 'qty'
FROM product

Answer 3

OK, with the help of two functions, we can parse your model numbers into digits and then get the sum of digits.

Select [dbo].[udf-Stat-Sum-of-Digits](12345)     -- Returns 15
Select [dbo].[dbo].[udf-Str-Numbers]('AF567-56') -- Returns 56756

The good news is that we can combine these as illustrated below

Declare @Table table (model varchar(50))
Insert into @Table values
('AF567-56'),
('25-a-467'),
('11156 25')

Select Model
      ,Digits = [dbo].[udf-Str-Numbers](Model)
      ,SumOfDigits = [dbo].[udf-Stat-Sum-of-Digits]([dbo].[udf-Str-Numbers](Model))
 From  @Table

Returns

Model       Digits  SumOfDigits
AF567-56    56756   29
25-a-467    25467   24
11156 25    1115625 21

The two UDFs

CREATE Function [dbo].[udf-Stat-Sum-of-Digits](@Val int)
Returns Int
As
Begin

Declare @RetVal as int

;with i AS (
    Select @Val / 10 n, @Val % 10 d
    Union ALL
    Select n / 10, n % 10
    From i
    Where n > 0
)
Select @RetVal = SUM(d) FROM i;

Return @RetVal

END

The second Function

CREATE FUNCTION [dbo].[udf-Str-Numbers](@String varchar(250))
Returns Varchar(250)
As
Begin
    Declare @RetVal varchar(250) = @String
    ;with cteChar as (Select Cnt=1,Str=Char(1) Union All Select Cnt=B.Cnt+1,Str=Char(B.Cnt+1) From cteChar as B Where B.Cnt <= 255)
    Select @RetVal = Replace(@RetVal,Str,'') From cteChar where str not like '[0-9]' Option (maxrecursion 256)
    Return case when IsNull(@RetVal,'')='' then @String else @RetVal end
END