Caluculate sum of digits in sql

Question

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.

Packiaraj Santhiyagu · 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 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'

Kapil Singh Kumawat · Answer

You can simply achieve this using loop.

Packiaraj Santhiyagu · Answer

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

Manish Saini · Answer

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 BEGIN SET @liCount = @liCount + 1 SET @lstrSub = (SELECT SUBSTRING(@liInput, @liCount , 1)) SET @liSubStr = @liSubStr + 1 SET @liSum = @liSum + (SELECT @lstrSub) PRINT @liSum END