Two Methods of MySql Comma Splicing String Query

The use of the following two functions and FIND_IN_SET 1, when using, you only need to put FIND_IN_SET Change to FIND_PART_IN_SET Or FIND_ALL_PART_IN_SET

For example, a field is 1, 2, 3, 4, 5

Usage:

The first type, incoming 1, 3 and 6 can be found out

select * from XXX where FIND_PART_IN_SET('1,3,6','1,2,3,4,5')

The second type, passing in 1, 3 and 6 can't be found

select * from XXX where FIND_ALL_PART_IN_SET('1,3,6','1,2,3,4,5')

Function:

Type 1: You can find out if you include one of them

CREATE DEFINER = `root`@`%` FUNCTION `NewProc`(str1 text,str2 text)
 RETURNS text
BEGIN
 # Pass in two comma-separated strings to judge the 2 Does the string contain the 1 String split After that, a single 
DECLARE CURRENTINDEX INT;# Current subscript 
DECLARE CURRENTSTR text;
DECLARE result int;
set result = 0;
set CURRENTINDEX = 0;
set CURRENTSTR = '';
IF str1 IS NOT NULL AND str1 != '' THEN
 SET CURRENTINDEX = LOCATE(',',str1);
 WHILE CURRENTINDEX > 0 DO
 SET CURRENTSTR = substring(str1,1,CURRENTINDEX-1);
 if FIND_IN_SET(CURRENTSTR,str2) THEN
  set result = 1;
 end if;
 SET str1 = substring(str1,CURRENTINDEX+1);
 SET CURRENTINDEX = LOCATE(',',str1);
 END WHILE;
 # Pass only 1 A   And   Finally, there is no comma 
 IF LENGTH(str1) > 0 THEN
 if FIND_IN_SET(str1,str2) THEN
  set result = 1;
 end if;
 END IF;
END IF;
RETURN result;
END;

Type 2: It must be completely included before it can be detected

CREATE DEFINER = `root`@`%` FUNCTION `NewProc`(str1 text,str2 text)
 RETURNS text
BEGIN
 # Pass in two comma-separated strings to judge the 2 Whether all strings contain the 1 String split After that, a single 
DECLARE CURRENTINDEX INT;# Current subscript 
DECLARE CURRENTSTR text;
DECLARE RESULT int;
DECLARE TOTALCOUNT int;
DECLARE TRUECOUNT int;
set RESULT = 0;
set CURRENTINDEX = 0;
set CURRENTSTR = '';
set TOTALCOUNT = 0;
set TRUECOUNT = 0;
IF str1 IS NOT NULL AND str1 != '' THEN
 SET CURRENTINDEX = LOCATE(',',str1);
 WHILE CURRENTINDEX > 0 DO
 SET TOTALCOUNT = TOTALCOUNT + 1;
 SET CURRENTSTR = substring(str1,1,CURRENTINDEX-1);
 if FIND_IN_SET(CURRENTSTR,str2) THEN
  SET TRUECOUNT = TRUECOUNT + 1;
 end if;
 SET str1 = substring(str1,CURRENTINDEX+1);
 SET CURRENTINDEX = LOCATE(',',str1);
 END WHILE;
 # Pass only 1 A   And   Finally, there is no comma 
 IF LENGTH(str1) > 0 THEN
 SET TOTALCOUNT = TOTALCOUNT + 1;
 if FIND_IN_SET(str1,str2) THEN
  SET TRUECOUNT = TRUECOUNT + 1;
 end if;
 END IF;
END IF;
IF TOTALCOUNT > 0 AND TRUECOUNT = TOTALCOUNT THEN 
 SET RESULT = 1;
END IF;
RETURN result;
END;

Summarize