Mysql generated columns as a count of comma separated values (CSV) of another column

I am trying to create a MYSQL generated column which automatically generates a value of the prescribed column as a count. the generated columns would be easy to set but the problem is that the prescribed column contains comma separated values (CSV).

till now i have been running the manual query into Mysql each time but I want the MYsql to automatically do this here is the manual update query that i am using

UPDATE department_table SET `department_capacity`=(SELECT        (CHAR_LENGTH(department_doctor) - CHAR_LENGTH(REPLACE(department_doctor, ',', '')) + 1) as total);  

Here is the MySQL command that I want to try so that the table is always auto generated

ALTER TABLE `department_table`   CHANGE `department_capacity`   `department_capacity` INT(11) NOT NULL GENERATED ALWAYS AS   (CHAR_LENGTH(department_doctor) - CHAR_LENGTH(REPLACE(department_doctor, ',', '') + 1) as total );  

however this shows an error messages as the alter operation was not recognized or unidentified delimiter ','.

here is my database tables

|department_id|department_name|department_capacity|department_doctor            |  |______________________________________________________________________________ |  |1            |Orthopaedie    |4                  |Leon , Dessie, Jayda, Prakhar|  |2            |Operation      |2                  |Hook,Haigh                   |  

https://stackoverflow.com/questions/65376969/mysql-generated-columns-as-a-count-of-comma-separated-values-csv-of-another-co December 20, 2020 at 12:40PM