How to sum MySQL varchar field value

Question

I have a Varchar field for total_amount, the field has something like 1+1+1

I need to sum this column.

Answer 1

Here is my solution that is based on a function for splitting a string that I've found on several places on the net so I am not sure who to credit for and a function that uses the split function to calculate a total

DELIMITER $$

CREATE FUNCTION `CalcTotal`(input VARCHAR(255)) RETURNS int(11)
DETERMINISTIC
BEGIN
  DECLARE pos int;
  DECLARE result int;
  DECLARE value_str VARCHAR(255);

  SET pos = 1;
  SET result = 0;
  SET value_str = '0';

  WHILE value_str <> '' DO
    SET value_str = SPLIT_STR(input, '+', pos);
    IF value_str <> '' THEN
      SET result = result + value_str;
      SET pos = pos + 1;
    END IF;
  END WHILE;

  RETURN (result);
END$$

CREATE FUNCTION `SPLIT_STR`(x VARCHAR(255), delim VARCHAR(12), pos INT) RETURNS varchar(255)
DETERMINISTIC
BEGIN 
  RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
         LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1), delim, '');
END$$

DELIMITER ;

To use it do something like

SELECT CalcTotal(total_amount) 
FROM some_table

Note that I have set the max length of the varchar to 255 and assumed only integer values but this can of course be modified