En FUNCTIONS.sql

/* Additional MySQL-functions and examples of their use */

/* MySQL-function which clears the entered number of all characters except the English letters and digits */

/* It is unnecessary, if you perform a similar cleanup of the user-entered number in the code, which calls this SQL-query - in many programming languages it can be done easier. */

DROP FUNCTION IF EXISTS CLEAN_NUMBER;
DELIMITER $$
CREATE FUNCTION CLEAN_NUMBER(STR VARCHAR(105))
RETURNS VARCHAR(105) DETERMINISTIC
BEGIN
DECLARE LEN INT DEFAULT LENGTH(STR);
DECLARE I INT DEFAULT 1;
DECLARE NEWSTR VARCHAR(105) DEFAULT ;
DECLARE C CHAR;
WHILE I<=LEN DO
SET C = SUBSTR(STR, I, 1);
IF C >= 'a' AND C <= 'z' OR		   C >= 'A' AND C <= 'Z' OR		   C >= '0' AND C <= '9' THEN	SET NEWSTR = CONCAT(NEWSTR, C);
END IF;
SET I = I+1;
END WHILE;
RETURN NEWSTR;
END$$
DELIMITER ;

/* Example of use */

SET @ARL_DISPLAY_NR = '078 115 561';
SET @ARL_DISPLAY_NR = CLEAN_NUMBER(@ARL_DISPLAY_NR);
SELECT @ARL_DISPLAY_NR;

• Back to index page: Tecdoc_queries_and_structure_manual_-_version_2

• Prices and packages for Irbis software based on tecdoc from Mstarproject company: http://www.mstarproject.com/en/?action=tecdoc_mysql_site