0

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);

or :

    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...

  • If you store a valid (My)SQL expression in that column, then dynamic SQL is a possible answer - you just create a string for the statement, insert the expression at the right place and then prepare the statement and execute it. Just make sure you do not use any user-defined text in there to not allow sql injection. – jkavalik Nov 11 '15 at 20:25
  • You posted a reponse but it disapeared, didn't it ? The text comes from an html form and php code to compute a value but keep details in a string and store them in a table. I am the only user of that form...'but it's a challenge to be secure against sql injection) . Students can have a look to another read only form (where i have to split the field to get each value...maybe database misconception...I will have a look to dynamic SQL, Thanks ! – Patrick Dezecache Nov 11 '15 at 20:52
1

for my specific problem, an answer could be :

    DROP FUNCTION stringExp2Int;
    DELIMITER $$
    CREATE FUNCTION stringExp2Int (expression VARCHAR(20))
        RETURNS INT
        NO SQL
        BEGIN
            DECLARE val INT DEFAULT 0;
            DECLARE ct INT DEFAULT 1;
            WHILE ct <= LENGTH(expression) do
                SET val = val + SUBSTR(expression,ct,1);
                SET ct = ct + 2;
            END WHILE;
             RETURN val;
        END
    $$
    DELIMITER ;

Using LOCATE to find the next separator, could be more secure :

    DROP FUNCTION stringExp2Int;
    DELIMITER $$
    CREATE FUNCTION stringExp2Int (expression VARCHAR(20))
        RETURNS INT
        NO SQL
    BEGIN
      DECLARE val INT DEFAULT 0;
      DECLARE ct INT DEFAULT 1;
      DECLARE pos INT DEFAULT 0;
      DECLARE sep CHAR(1) DEFAULT "+";

      WHILE ct <= LENGTH(expression) DO
         SET pos = LOCATE(sep,SUBSTR(expression,ct,LENGTH(expression)-ct+1));
         IF pos >= 1 THEN 
            BEGIN -- when pos = 1 : add a null value
                SET val = val + SUBSTR(expression,ct,pos-1);
                SET ct = ct + pos;
            END;
        ELSE 
            BEGIN
                SET val = val + SUBSTR(expression,ct,LENGTH(expression)-ct+1);
                SET ct = ct + LENGTH(expression)-ct+1;
            END;
        END IF;
     END WHILE;
     RETURN val;
    END
    $$
    DELIMITER ;

Trials :

mysql> SELECT stringExp2Int("");
+-------------------+
| stringExp2Int("") |
+-------------------+
|                 0 |
+-------------------+

mysql> SELECT stringExp2Int("+");
+--------------------+
| stringExp2Int("+") |
+--------------------+
|                  0 |
+--------------------+

mysql> SELECT stringExp2Int("1");
+--------------------+
| stringExp2Int("1") |
+--------------------+
|                  1 |
+--------------------+

mysql> SELECT stringExp2Int("+1");
+---------------------+
| stringExp2Int("+1") |
+---------------------+
|                   1 |
+---------------------+

mysql> SELECT stringExp2Int("1+");
+---------------------+
| stringExp2Int("1+") |
+---------------------+
|                   1 |
+---------------------+

mysql> SELECT stringExp2Int("1+1");
+----------------------+
| stringExp2Int("1+1") |
+----------------------+
|                    2 |
+----------------------+

mysql> SELECT stringExp2Int("+1+1+");
+------------------------+
| stringExp2Int("+1+1+") |
+------------------------+
|                      2 |
+------------------------+

mysql> SELECT stringExp2Int("+++1+++1+++");
+------------------------------+
| stringExp2Int("+++1+++1+++") |
+------------------------------+
|                            2 |
+------------------------------+

mysql> SELECT stringExp2Int("+++001+++001+++");
+----------------------------------+
| stringExp2Int("+++001+++001+++") |
+----------------------------------+
|                                2 |
+----------------------------------+

and :

mysql> SELECT stringExp2Int(detailNote) FROM A;
+---------------------------+
| stringExp2Int(detailNote) |
+---------------------------+
|                        16 |
+---------------------------+
1 row in set (0.00 sec)

I close this question...hope it could be useful for someone...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.