DELIMITER $$


DROP FUNCTION IF EXISTS `uReplaceNon_Numer`$$


CREATE FUNCTION `uReplaceNon_Numer`(in_phone varchar(50)) RETURNS varchar(50) CHARSET latin1

    NO SQL

BEGIN

DECLARE ctrNumber varchar(50);

DECLARE finNumber varchar(50) default ' ';

DECLARE sChar varchar(2);

DECLARE inti INTEGER default 1;

-- RETURN 'hello1';

    IF length(in_phone) > 0 THEN

-- RETURN 'hello2';

	WHILE(inti <= length(in_phone)) DO

-- RETURN 'hello3';

		SET sChar= SUBSTRING(in_phone,inti,1);

-- RETURN sChar;

                SET ctrNumber= FIND_IN_SET(sChar,'0,1,2,3,4,5,6,7,8,9');

-- RETURN ctrNumber;



                IF ctrNumber > 0 THEN

			SET finNumber=CONCAT(finNumber,sChar);

-- RETURN CONCAT('in if',finNumber);

                ELSE

			SET finNumber=CONCAT(finNumber,' ');

-- RETURN CONCAT('in else',finNumber);

                END IF;

		SET inti=inti+1;

	END WHILE;

	RETURN finNumber;

    ELSE

	RETURN 'Invalid';

    END IF;



END$$


DELIMITER ;


## Usage

SELECT b.bolNum, (uReplaceNon_Numer(r.barcodeRangeMax) - uReplaceNon_Numer(r.barcodeRangeMin)) barcodes FROM wh_bol_range r INNER JOIN wh_bol b ON (b.bolId=r.bolId) ORDER BY barcodes DESC LIMIT 0,20;