Abdul Rehman Janjua Abdul Rehman Janjua - 2 months ago 10
MySQL Question

ASCII sum of all the all the characters in column Mysql

I have a table users but i have shown only 2 columns I want to sum all the characters of name column.

+----+-------+
| id | name |
+----+-------+
| 0 | user |
| 1 | admin |
| 3 | edit |
+----+-------+


for example ascii sum of user will be
sum(user)=117+115+101+114=447

i have tired this

SELECT ASCII(Substr(name, 1,1)) + ASCII(Substr(name, 2, 1)) FROM user


but it only sums 2.

Answer

You are going to have to fetch one character at a time to do the sum. One method is to write a function with a while loop. You can do this with a SELECT, if you know the longest string:

SELECT name, SUM(ASCII(SUBSTR(name, n, 1)))
FROM user u JOIN
     (SELECT 1 as n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL 
      SELECT 4 UNION ALL SELECT 5 -- sufficient for your examples
     ) n
     ON LENGTH(name) <= n.n
GROUP BY name;

If your goal is to turn the string as something that can be easily compared or a fixed length, then you might consider the encryption functions in MySQL. Adding up the ASCII values is not a particularly good hash function (because strings with the same characters in different orders produce the same value). At the very least, multiplying each ASCII value by the position is a bit better.

Comments