Calculate sum alphabets with given string?

Sai Reddy

8y1.6k

I have a requirement as follows.

a=1,b=2,c=3,d=4,e=5....etc

String=Praveen(passing it as parameter in stored procedure)

i need to calculate each character alphbitacal number and sum of them

ex: 1+15+30+9+1+2=58

result is 5+8=13

i need it as a stored procedure in mysql and sql server?

15 Replies

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

Nitin Sontakke8y ago
Accepted answer
Here is the script, which you can easily convert to the stored procedure:
declare @name varchar(50) = 'Praveen';

select @name = lower(@name);

declare @Length integer = len(@name), @index integer = 1, @total integer = 0;

while @index < @Length + 1
  begin
    select @total += (ascii(substring(@name, @index, 1)) - 96)
    select @index += 1;
  end

declare @totalString varchar(5) = ltrim(str(@total));
select @Length = len(@totalString), @index = 1, @total = 0;

while @index < @Length + 1
  begin
    select @total += substring(@totalString, @index, 1)
    select @index += 1;
  end

select @total
0
Nitin Sontakke8y ago
@Sai, are you 100% sure that the accepted answer is giving you an expected output? If yes, then may be your question is not been put correctly.

Please confirm.
0
Packiaraj Santhiyagu8y ago
Hi Sai

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 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
Nitin Sontakke8y ago
Unfortunately, I am not quite conversant with language used in MySql. Sorry!

You will probably have to work that out. If you are learning, this should be a pretty easy exercise, as the code is very basic.

Find out all equivalent functions for len, str, ascii, substring, etc.
0
Sai Reddy8y ago
Thank you Nitin, how can we do the same in mysql as i am working on mysql.
0
Sai Reddy8y ago
Can any one please help me?
The procedure can be in mysql or tsql no problem.
0
Sai Reddy8y ago
Hi Nitin Sontakke

Yes, Both A and a are having same value 1
B and b=2
C and c=3
..
Z and z=4
0
Sai Reddy8y ago
Example
I have passed string through parameter like='abcde'
then it has to count alphabitical values for the string like 1+2+3+4+5=15
final result should be 1+5=6

I have created a table

ID Alpha
1 A
2 B
3 C
4 D
... ....
0
Nanddeep Nachan8y ago
I guess, case insensitive calculation of alphabet to numbers
0
Nitin Sontakke8y ago
You didn't answer my first question.

Is 'A' and 'a' both have value 1 or different? If different then what?
0
Nanddeep Nachan8y ago
1. DECLARE @dummy TABLE(AlphaNumber VARCHAR(10));
2. INSERT INTO @dummy VALUES ('PRAVEEN');
4. SELECT CAST(CAST(AlphaNumber AS VARBINARY) AS INT)
5. FROM @dummy;
0