Calculate sum alphabets with given string?

Question

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?

Nitin Sontakke · 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 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 begin select @total += substring (@totalString, @ index , 1) select @ index += 1; end select @total

Nitin Sontakke · Answer

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

Packiaraj Santhiyagu · Answer

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

Nitin Sontakke · Answer

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.

Sai Reddy · Answer

Thank you Nitin, how can we do the same in mysql as i am working on mysql.

Sai Reddy · Answer

Can any one please help me? The procedure can be in mysql or tsql no problem.

Sai Reddy · Answer

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

Sai Reddy · Answer

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

Nanddeep Nachan · Answer

I guess, case insensitive calculation of alphabet to numbers

Nitin Sontakke · Answer

You didn't answer my first question. Is 'A' and 'a' both have value 1 or different? If different then what?

Nanddeep Nachan · Answer

DECLARE @dummy TABLE (AlphaNumber VARCHAR (10)); INSERT INTO @dummy VALUES ( 'PRAVEEN' ); SELECT CAST ( CAST (AlphaNumber AS VARBINARY) AS INT ) FROM @dummy;

Sai Reddy · Answer

The string can be anything, i have just given a example and A=a=1 and Z=z=26

Nitin Sontakke · Answer

@Nanddeep, OP wants the MySql and / or SQL Server code. I do agree in principle that the logic would still be the same, though.

Nitin Sontakke · Answer

Is there a different value for 'A' and 'a' or both are 1? And values given by you in example, are just for example and DO NOT corrospond to Praveen. Right?

Nanddeep Nachan · Answer

Use below code to convert letter to number static void Main( string [] args) { int v= LetterToNumber( 'd' ); Console.WriteLine(v); } private static int LetterToNumber( char letter) { int index = char .ToUpper(letter) - 64; return index; } You can develop rest of the logic.