SQL/MySQL/Procedure Function/DETERMINISTIC

DETERMINISTIC function

   <source lang="sql">

mysql> mysql> delimiter $$ mysql> mysql> CREATE FUNCTION count_strings

   ->       (in_string VARCHAR(256),in_substr VARCHAR(128))
   ->   RETURNS INT
   ->   DETERMINISTIC
   -> BEGIN
   ->   DECLARE l_count INT DEFAULT 0;
   ->   DECLARE l_start INT DEFAULT 1;
   ->   DECLARE l_pos   INT;
   ->
   ->   MainLoop:
   ->   LOOP
   ->     SET l_pos=LOCATE(in_substr,in_string,l_start);
   ->     IF l_pos=0 THEN
   ->        LEAVE MainLoop;
   ->     ELSE
   ->       SET l_count=l_count+1;
   ->       SET l_start=l_pos+1;
   ->     END IF;
   ->
   ->   END LOOP;
   ->   RETURN(l_count);
   -> END$$

Query OK, 0 rows affected (0.00 sec) mysql> mysql> delimiter ; mysql> select count_strings("asdf,asdfasdff,asdf,asdfasdf,","asd"); +------------------------------------------------------+ | count_strings("asdf,asdfasdff,asdf,asdfasdf,","asd") | +------------------------------------------------------+ | 6 | +------------------------------------------------------+ 1 row in set (0.02 sec) mysql> drop function count_strings; Query OK, 0 rows affected (0.00 sec) mysql> mysql>

       </source>