Finding the SUM of digits mapped to characters of a string

Question

I have a table with 2 columns, one column with Alphabetic character and other column with value. Example A 1 B 2 C 3 D 4 E 5 Now I would like to get the sum of all digits corresponding to characters containing in a string. Like BAD = (2+1+4) = 7 Please suggest how this can be done in mysql (sql/procedure)

Answer 1

You can use the following solution:

I used the following to setup the table with the characters and their numeric values:

-- create the table for char values.
CREATE TABLE charValues (
    charItem VARCHAR(1),
    charValue INT
);

-- insert the values to the table.
INSERT INTO charValues VALUES
    ('A', 1),
    ('B', 2),
    ('C', 3),
    ('D', 4),
    ('E', 5);

To get the count of each character on your value, you can use the following FUNCTION (in this case named getCharCount). This FUNCTION was created on the following solution and can be used in this case to get the count of each character:

-- function to count the count of a character.
CREATE FUNCTION getCharCount (colValue VARCHAR(255), searchValue CHAR(1)) 
RETURNS INT DETERMINISTIC
RETURN (CHAR_LENGTH(colValue) - CHAR_LENGTH(REPLACE(colValue, searchValue, '')));

Now you can add a second FUNCTION to get the SUM of the value:

DELIMITER //

CREATE FUNCTION getStrSum (colValue VARCHAR(255))
RETURNS INT NO SQL
BEGIN
    DECLARE retVal INT;
    SELECT SUM(getCharCount(colValue, charItem) * charValue) INTO retVal FROM charValues;
    RETURN retVal;
END //

Now you can use the following SELECT statement to get the calculated result based on table charValues. The functions can be used on the whole database like this:

SELECT getStrSum('BAD')  -- output: 7
SELECT getStrSum('DAD')  -- output: 9