Is it possible to evaluate a string value as a computed expression in an SQL statement like EVAL in some programming languages ?
>>> EDIT 4 : more precisely : an expression that contains a list of numbers separated by an arithmetic operator... <<<
For example : Table A :
CREATE TABLE A ( id INT NOT NULL PRIMARY KEY, note INT, detailNote VARCHAR(20) );
insert a row :
INSERT INTO A (id, note, detailNote) VALUES (1,20,"2+4+2+2+2+0+2+0+2");
I would like to be able to update 'note' column with the value of the evaluation of 'detailNote' column such as :
UPDATE A SET note = CAST(detailNote AS UNSIGNED);
UPDATE A SET note = 1*(detailNote);
These 2 trials produce an error : "truncated incorrect integer/double value ..."
Many thanks for any help !
>>> EDIT. 1 <<< I am aware that the DB model is NOT the right one; it should be :
CREATE TABLE A ( id INT NOT NULL PRIMARY KEY, note INT ); CREATE TABLE B ( id INT NOT NULL REFERENCES A(id), seq INT NOT NULL, detNote INT, PRIMARY KEY (id, seq) ); INSERT INTO A (id, note) VALUES (1,20); INSERT INTO B (id, seq, detNote) VALUES (1,1,2); INSERT INTO B (id, seq, detNote) VALUES (1,2,4); INSERT INTO B (id, seq, detNote) VALUES (1,3,2);
etc. and the update query could be :
UPDATE A SET note = (SELECT SUM(detNote) FROM B WHERE A.id = B.id);
But, the use of the string is more compact and the content is sure.
>>> EDIT. 2 <<< 1st solution (bad one, but exists...) : (because I am sure of integer (0-9) position :
SELECT (SUBSTR(detailNote,1,1) +SUBSTR(detailNote,3,1) + SUBSTR(detailNote,5,1) +SUBSTR(detailNote,7,1) + SUBSTR(detailNote,9,1) +SUBSTR(detailNote,11,1) + SUBSTR(detailNote,13,1) +SUBSTR(detailNote,15,1) + SUBSTR(detailNote,17,1) +SUBSTR(detailNote,19,1)) FROM A;
>>> EDIT. 3 <<< one could tell me to add 10 INT fields in A...but one string is easier to manipulate...