yq966D4s

1MySQL INSERT with CALL
2INSERT INTO ....
3CALL sp_split...
4	
5DELIMITER $$
6
7CREATE DEFINER=`root`@`localhost` PROCEDURE `split_with_id`(id INT, input varchar(1000), delim VARCHAR(10))
8BEGIN
9    declare foundPos tinyint unsigned; 
10    declare tmpTxt varchar(1000); 
11    declare delimLen tinyint unsigned; 
12    declare element varchar(1000); 
13
14drop temporary table if exists tmpValues; 
15create temporary table tmpValues 
16( 
17    `id` int not null default 0,
18    `values` varchar(1000) not null default '' 
19) engine = memory; 
20
21set delimLen = length(delim); 
22set tmpTxt = input; 
23
24set foundPos = instr(tmpTxt,delim); 
25
26while foundPos <> 0 do 
27    set element = substring(tmpTxt, 1, foundPos-1); 
28    set tmpTxt = replace(tmpTxt, concat(element,delim), ''); 
29
30
31    insert into tmpValues (`id`, `values`) values (id, element); 
32
33    set foundPos = instr(tmpTxt,delim); 
34end while; 
35
36if tmpTxt <> '' then 
37    insert into tmpValues (`id`, `values`) values (id, tmpTxt); 
38end if; 
39
40select * from tmpValues;
41	
42DELIMITER $$
43
44CREATE FUNCTION `f_wrapper_split` (strin VARCHAR(255))
45RETURNS VARCHAR(255)
46BEGIN
47    DECLARE r VARCHAR(255);
48    CALL sp_split(strin);
49    RETURN r;
50END
51$$