Use MySQL stored procedure to split strings

The MySQL stored procedure can be used to split strings. The following describes the usage of this MySQL Stored Procedure for your reference.

A string, such as apple, banana, orange, pears, and grape, must be separated by commas (,):

Apple
Banana
Orange
Pears
Grape

Then you can use the where in () method to query.

1. Specific functions:

 
 

  
  
# Function: func_split_TotalLength
  
  
DELIMITER $
  
  
DROP function if exists 'func_split_totallength '$
  
  
Create definer = 'root' @ '% 'function' func _ split_TotalLength'
  
  
(F_string varchar (1000), f_delimiter varchar (5) RETURNS int (11)
  
  
BEGIN
  
  
# Calculate the total length of the input string
  
  
Return 1 + (length (f_string)-length (replace (f_string, f_delimiter ,'')));
  
  
END $
  
  
DELIMITER;
 
 

 
 

  
  
# Function: func_split
  
  
DELIMITER $
  
  
DROP function if exists 'func_split '$
  
  
Create definer = 'root' @ '% 'function' func _ split'
  
  
(F_string varchar (1000), f_delimiter varchar (5), f_order int) RETURNS varchar (255) CHARSET utf8
  
  
BEGIN
  
  
# Split the input string and return the new string after splitting.
  
  
Declare result varchar (255) default '';
  
  
Set result = reverse (substring_index (f_string, f_delimiter, f_order), f_delimiter, 1 ));
  
  
Return result;
  
  
END $
  
  
DELIMITER;
 
 

 
 

  
  
# Stored procedure: splitString
  
  
DELIMITER $
  
  
Drop procedure if exists 'splitstring' $
  
  
Create procedure 'splitstring'
  
  
(IN f_string varchar (1000), IN f_delimiter varchar (5 ))
  
  
BEGIN
  
  
# Splitting result
  
  
Declare cnt int default 0;
  
  
Declare I int default 0;
  
  
Set cnt = func_split_TotalLength (f_string, f_delimiter );
  
  
Drop table if exists 'tmp _ split ';
  
  
Create temporary table 'tmp _ split '('status' varchar (128) not null) default charset = utf8;
  
  
While I <cnt
  
  
Do
  
  
Set I = I + 1;
  
  
Insert into tmp_split ('status') values (func_split (f_string, f_delimiter, I ));
  
  
End while;
  
  
END $
  
  
DELIMITER;
 
 

2. Test whether segmentation is successful

 
 

  
  
call splitString("apple,banana,orange,pears,grape",","); 
  
  
select * from tmp_split; 
 
 

The running result is as follows, indicating that the splitting is successful:

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. Application where in () Query

 
 

  
  
# Input a string, split it, and save it in the temporary table tmp_split.
  
  
Call splitString ("apple, banana, orange, pears, grape ",",");
  
  
# Use the query result as a condition for other queries
  
  
Select * from fruit where in (select * from tmp_split );
 
 

The preceding section describes how to use the MySQL stored procedure to split strings.

MySQL string addition function usage example

MySQL string truncation Function Method

MySQL string segmentation implementation

In-depth study of MySQL result strings

How to quickly enable MySQL slow log query