MySQL Function ONLY_NUMBERS

Question

Any help here will be appreciate!

I need a function in MySQL like I have here in my PHP libs, I call that as "onlyNumbers", this function loops all the string filtering numbers and concate as a return string.

I could follow the logic of MySQL syntax and reproduce that, as you see bellow, but there is some thing missing, I think a trick, or some simple step that not comes to my mind.

When run this in MySQL the function is created but when I call that I got this error: #2014 - Commands out of sync; you can't run this command now

Here is my function code:

DROP FUNCTION IF EXISTS ONLY_NUMBERS;
DELIMITER $$
CREATE FUNCTION ONLY_NUMBERS(_STR TEXT) RETURNS TEXT DETERMINISTIC
    BEGIN
        DECLARE NUMBERS TEXT;
        DECLARE _STR_CHAR CHAR(1) DEFAULT NULL;
        DECLARE _STR_POS BIGINT(20) DEFAULT 0;
        DECLARE _STR_INIT BIGINT(20) DEFAULT 0;
        DECLARE _STR_ENDS BIGINT(20) DEFAULT 0;
        SET _STR_ENDS = CHAR_LENGTH(_STR);
        STRING_LOOP: LOOP
            IF _STR_POS = STR_ENDS THEN
                LEAVE STRING_LOOP;
            END IF;
            SET _STR_CHAR = SUBSTR(_STR,_STR_POS,1);
            IF _STR_CHAT REGEXP '^[0-9]$' THEN
                SET NUMBERS = CONCAT(NUMBERS, _STR_CHAR);
            END IF;
            SET _STR_POS = _STR_POS + 1;
            ITERATE STRING_LOOP;
        END LOOP STRING_LOOP;
        RETURN NUMBERS;
    END $$
DELIMITER ;

When I call SELECT ONLY_NUMBERS('sadfasdf3423452345234'); I am expecting to see 3423452345234 as result

Newton Pasqualini Filho Newton Pasqualini Filho · Accepted Answer · 2015-02-20T13:09:26

Ok, here we go!

After many, many, many tryies, a got what I expected!

This is a function that return only numbers from a string as argument, I made some improvements with speed, weight, less declares, less memory, etc...

Here are the function, remenber this is using a core MySQL Server you donnot need to install anything: DROP FUNCTION IF EXISTS ONLY_NUMBERS; DELIMITER // CREATE FUNCTION ONLY_NUMBERS(_STR TEXT) RETURNS TEXT DETERMINISTIC BEGIN DECLARE _NUMBERS TEXT; DECLARE _STR_POS BIGINT(20) DEFAULT 0; DECLARE _STR_ENDS BIGINT(20) DEFAULT 0; SET _STR_ENDS = CHAR_LENGTH(_STR); SET _NUMBERS = ''; WHILE _STR_POS <= _STR_ENDS DO IF SUBSTR(_STR, _STR_POS, 1) REGEXP '^[0-9]$' THEN SET _NUMBERS = CONCAT(_NUMBERS, SUBSTR(_STR, _STR_POS, 1)); END IF; SET _STR_POS = _STR_POS + 1; END WHILE; RETURN _NUMBERS; END // DELIMITER ;

MySQL Function ONLY_NUMBERS

1 Answers