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 |
SELECT ASCII(Substr(name, 1,1)) + ASCII(Substr(name, 2, 1)) FROM user
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.