Splitting strings using MySQL stored procedures

(EXT) http://tec.5lulu.com/detail/104krn1e6p2w78d77.html

An existing string, such as Apple,banana,orange,pears,grape, to be separated by commas (,):

Apple
Banana
Orange
Pears
Grape

Then use the Where in () method to query.

1, the specific function:

2. # function: Func_split_totallength
4. DELIMITER $$
6. DROP function IF EXISTS ' func_split_totallength ' $$
8. CREATE definer=' root '@'% ' FUNCTION ' func_split_totallength '
10. (f_string varchar (+),f_delimiter varchar(5)) RETURNS int( One )
12. BEGIN
14. # Calculates the total length of the incoming string
16. return 1+ (length(f_string) - length(replace(f_ String,f_delimiter," ));
18. END$$
20. DELIMITER;

2. # function: Func_split
4. DELIMITER $$
6. DROP function IF EXISTS ' func_split ' $$
8. CREATE definer=' root '@'% ' FUNCTION ' func_split '
10. (f_string varchar (+),f_delimiter varchar(5),f_order int ) RETURNS varchar(255) CHARSET UTF8
12. BEGIN
14. # Splits the passed-in string, returning the new string after splitting
16. DECLARE result varchar(255) default ';
18. set Result = reverse(substring_index (reverse (substring_index (f) _string,f_delimiter,f_order)),f_delimiter,1);
20. return result;
22. END$$
24. DELIMITER;

2. # Stored Procedure : splitstring
4. DELIMITER $$
6. DROP PROCEDURE IF EXISTS ' splitstring ' $$
8. CREATE PROCEDURE ' splitstring '
10. (in f_string varchar (+), inf_delimiter varchar(5))
12. BEGIN
14. # Split Results
16. DECLARE CNT int default 0;
18. DECLARE i int default 0;
20. Set cnt = func_split_totallength(f_string,f_delimiter);
22. DROP TABLE IF EXISTS ' tmp_split ';
24. Create temporary table ' Tmp_split ' (' status ' varchar(+) not null ) DEFAULT CHARSET=UTF8;
26. While I < cnt
28. Do
30. set i = i + 1;
32. Insert into tmp_split(' status ') values (func_split(f_string ,f_delimiter,i));
34. End while;
36. END$$
38. DELIMITER;

2. Whether the test can be successfully segmented

1. Call splitstring("Apple,banana,orange,pears,grape",",");
2. Select * from tmp_split;

The results of the operation are as follows, stating that the segmentation succeeded:

Mysql> call Splitstring ("Apple,banana,orange,pears,grape", ",");
SELECT * from Tmp_split;
Query OK, 1 row affected

+--------+
| Status |
+--------+
| Apple |
| Banana |
| Orange |
| Pears |
| Grape |
+--------+
5 rows in Set

Mysql>

3. Apply where in () query

1. # first pass in string, save in temp table tmp_split
2. call Splitstring ( "Apple,banana,orange,pears,grape" ,
3. # use the results of the query as conditions for other queries using
4. select * fruit where in ( Span class= "KWD" >select * from Tmp_split

Splitting strings with MySQL stored procedures