CNpTGGh0

1delimiter //
2DROP PROCEDURE IF EXISTS split_string;//
3
4CREATE PROCEDURE split_string (
5    IN input TEXT,
6    IN delimiter VARCHAR(10)
7)
8    SQL SECURITY INVOKER
9    BEGIN
10      DECLARE cur_position INT DEFAULT 1 ;
11      DECLARE remainder TEXT;
12      DECLARE cur_string VARCHAR(1000);
13      DECLARE delimiter_length TINYINT UNSIGNED;
14
15      DROP TEMPORARY TABLE IF EXISTS SplitValues;
16
17      CREATE TEMPORARY TABLE SplitValues (
18        value VARCHAR(1000) NOT NULL PRIMARY KEY
19      ) ENGINE=MEMORY;
20
21      SET remainder = input;
22      SET delimiter_length = CHAR_LENGTH(delimiter);
23
24      WHILE CHAR_LENGTH(remainder) > 0 AND cur_position > 0 DO
25         SET cur_position = INSTR(remainder, delimiter);
26
27         IF cur_position = 0 THEN
28            SET cur_string = remainder;
29         ELSE
30            SET cur_string = LEFT(remainder, cur_position - 1);
31         END IF;
32
33         IF TRIM(cur_string) != '' THEN
34            INSERT INTO SplitValues VALUES (cur_string);
35         END IF;
36
37         SET remainder = SUBSTRING(remainder, cur_position +
38delimiter_length);
39      END WHILE;
40   END;//
41
42call split_string('unu,doi, trei', ',');//
43
44#select * from SplitValues;//
45
46
47drop procedure IF EXISTS ordoneaza_interese;//
48
49CREATE PROCEDURE ordoneaza_interese(IN cid INT)
50BEGIN
51
52  DECLARE done INT DEFAULT 0;
53  DECLARE rec_cursor TEXT;
54  DECLARE curs CURSOR FOR select `interese` from users where `comunitate_id`=cid;
55  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  
56
57  OPEN curs;
58
59  REPEAT
60    FETCH curs INTO rec_cursor;
61    IF NOT done THEN
62       split_string(rec_cursor, ",");
63			 #SELECT 	rec_cursor;       
64    END IF;
65  UNTIL done END REPEAT;
66
67  CLOSE curs;    
68	select * from SplitValues;
69END;//
70
71call ordoneaza_interese(11);