Caluculate sum of digits in sql

Enni

8y2k

Hi, i am new to sql server , i find a requirement some where in the google, please help me.

Q1:12345 (passed as a parameter in stored procedure)

Result: 15

How to acheive it?

Q2: 'ABCDE'

Find sum of ascii values of each charcter in the string.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Packiaraj Santhiyagu8y ago
Accepted answer
Hi Enni,
I work out and bring the result. It will work sure.

I create this SP in MSSQL server. So, run in MSSQL server

If you need any clarification, please comment immediately.

I command everything for your understanding.

create PROC [dbo].[sumOfDigit](

@passingdigit varchar(100)

)

AS

BEGIN

    --Declaration  part

    declare @index int

    declare @len int

    declare @char char(1)

    declare @total int





    -- value setting part

    set @index = 1 --by default set index=1

    set @len= LEN(@passingdigit)

    set @total=0;





    -- Looping here

    WHILE @index<= @len

    BEGIN

        set @char = SUBSTRING(@passingdigit, @index, 1) -- it will pick current index charater

        set @total+=@index -- here sum the value

        SET @index= @index+ 1 -- increment the loop value

    END

    SELECT @total -- print the result

END

execute the SP using below command

exec [sumOfDigit] @passingdigit='12345'
+1
Packiaraj Santhiyagu8y ago
Try this for string
CREATE PROC calstringValue(
@passedString varchar(100)
)
AS
BEGIN
--Declaration part
declare @index int
declare @len int
declare @char char(1)
declare @total int
declare @charvalue int
-- value setting part
set @index = 1 --by default set index=1
set @len= LEN(@passedString)
set @total=0;
set @charvalue=0;
-- Looping here
WHILE @index<= @len
BEGIN
set @char = SUBSTRING(@passedString, @index, 1) -- it will pick current index charater
set @char=lower(@char) -- convert character as lower case
set @charvalue=ASCII(@char)-96 -- ASCII value start from 97 for 'a' so i minus 96
set @total+=@charvalue -- here sum the value
SET @index= @index+ 1 -- increment the loop value
END
SELECT @total -- print the result
END
execute the SP using below command
EXEC calstringValue @passedString='PACKIA'
0
Kapil Singh Kumawat8y ago
You can simply achieve this using loop.
0
Manish Saini8y ago
You can use something like this
DECLARE @liInput varchar(20) = '12345'
DECLARE @liSubStr INT = 0;
DECLARE @lstrSub varchar(2)
DECLARE @liLenght int
DECLARE @liCount int = 0
DECLARE @liValue int = 0
DECLARE @liSum INT = 0;
set @liLenght = (select len(@liInput))
WHILE @liCount < @liLenght
BEGIN
SET @liCount = @liCount + 1
SET @lstrSub = (SELECT SUBSTRING(@liInput, @liCount , 1))
SET @liSubStr = @liSubStr + 1
SET @liSum = @liSum + (SELECT @lstrSub)
PRINT @liSum
END
0