Mega Code Archive
DETERMINISTIC function
<![CDATA[
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>
]]>