jeudi 23 avril 2015

MySQL Trim only works in certain cases in function definition

I have a function I'm troubleshooting in MySQL where I'm using TRIM. The problem seems to be that TRIM can handle certain "easy" strings, but not others. (The specifics of the rest of the function aren't that important. In other words, running the function with any two valid inputs will give the same output since the output is hard-coded.) For example, when the line "SET amts_sep_by_delim2 = trim(LEADING '1' FROM '12');" uses '12', '2' is returned when the function is called. However, when that line is changed to "SET amts_sep_by_delim2 = trim(LEADING '1' FROM '1000.00?---ase1000.00?5000');", the value returned by the function is "0".

Any ideas?

DROP FUNCTION IF EXISTS SUM_LIST;
DELIMITER //
CREATE FUNCTION SUM_LIST (amts_sep_by_delim VARCHAR(50), delim CHAR(1))
    RETURNS DOUBLE
    DETERMINISTIC
    BEGIN
        DECLARE i INT;
        DECLARE amt DOUBLE;
        DECLARE run_total DOUBLE;
        DECLARE amts_sep_by_delim2 VARCHAR(50);

        SET i = POSITION(delim IN amts_sep_by_delim);
        SET amt = SUBSTRING_INDEX(amts_sep_by_delim, delim, 1);
        SET run_total = amt;

        -- WHILE i > 0 DO
            SET amts_sep_by_delim2 = trim(LEADING '1' FROM '12'); -- TRIM(LEADING CONCAT(amt, delim) FROM amts_sep_by_delim);
            SET amt = SUBSTRING_INDEX(amts_sep_by_delim, delim, 1);
            SET i = POSITION(delim IN amts_sep_by_delim);
            SET run_total = run_total + amt;
        -- END WHILE;
     RETURN amts_sep_by_delim2;
    END;
    //

Aucun commentaire:

Enregistrer un commentaire