En FUNCTIONS.sql — различия между версиями

Материал из MstarProject Manual
Перейти к: навигация, поиск
 
Строка 15: Строка 15:
 
  DECLARE C CHAR;
 
  DECLARE C CHAR;
 
  WHILE I<=LEN DO
 
  WHILE I<=LEN DO
−
SET C = SUBSTR(STR, I, 1);
+
SET C = SUBSTR(STR, I, 1);
−
IF C >= 'a' AND C <= 'z' OR
+
IF C >= 'a' AND C <= 'z' OR   C >= 'A' AND C <= 'Z' OR   C >= '0' AND C <= '9' THEN SET NEWSTR = CONCAT(NEWSTR, C);
−
  C >= 'A' AND C <= 'Z' OR
+
END IF;
−
  C >= '0' AND C <= '9' THEN
+
SET I = I+1;
−
SET NEWSTR = CONCAT(NEWSTR, C);
+
END WHILE;
−
END IF;
+
RETURN NEWSTR;
−
SET I = I+1;
+
−
END WHILE;
+
−
 
+
−
RETURN NEWSTR;
+
 
  END$$
 
  END$$
 
  DELIMITER ;
 
  DELIMITER ;

Текущая версия на 10:38, 15 мая 2013

/* 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;


Источник — «http://manual.mstarproject.com/index.php?title=En_FUNCTIONS.sql&oldid=1251»