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

Материал из MstarProject Manual
Перейти к: навигация, поиск
(Created page with "/* 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...")
 
 
(не показана одна промежуточная версия этого же участника)
Строка 10: Строка 10:
 
  RETURNS VARCHAR(105) DETERMINISTIC
 
  RETURNS VARCHAR(105) DETERMINISTIC
 
  BEGIN
 
  BEGIN
−
DECLARE LEN INT DEFAULT LENGTH(STR);
+
DECLARE LEN INT DEFAULT LENGTH(STR);
−
DECLARE I INT DEFAULT 1;
+
DECLARE I INT DEFAULT 1;
−
DECLARE NEWSTR VARCHAR(105) DEFAULT '';
+
DECLARE NEWSTR VARCHAR(105) DEFAULT '';
−
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   C >= 'A' AND C <= 'Z' OR   C >= '0' AND C <= '9' THEN SET NEWSTR = CONCAT(NEWSTR, C);
−
IF C >= 'a' AND C <= 'z' OR
+
END IF;
−
  C >= 'A' AND C <= 'Z' OR
+
SET I = I+1;
−
  C >= '0' AND C <= '9' THEN
+
END WHILE;
−
SET NEWSTR = CONCAT(NEWSTR, C);
+
RETURN NEWSTR;
−
END IF;
+
−
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»