Two methods of MySql comma concatenation string query

The following two functions are used in the same way as FIND_IN_SET . When using them, you only need to replace FIND_IN_SET FIND_PART_IN_SET or FIND_ALL_PART_IN_SET

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

Directions:

The first one, pass in 1, 3, 6 to find out

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

The second type, passing in 1, 3, 6, cannot be found

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

function:

The first type: as long as one of them is included, it can be detected

CREATE DEFINER = `root`@`%` FUNCTION `NewProc`(str1 text,str2 text)
 RETURNS text
BEGIN
 #Pass in two comma-separated strings and determine whether the second string contains the single character after the split of the first string 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;
 #Only pass one and no comma at the end IF LENGTH(str1) > 0 THEN
 if FIND_IN_SET(str1,str2) THEN
  set result = 1;
 end if;
 END IF;
END IF;
RETURN result;
END;

The second type: All must be included to be detected

CREATE DEFINER = `root`@`%` FUNCTION `NewProc`(str1 text,str2 text)
 RETURNS text
BEGIN
 #Pass in two comma-separated strings to determine whether the second string contains all the single characters after the split of the first string 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;
 #Only pass one and no comma at the end 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

The above are two methods of MySql comma concatenation string query introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!

You may also be interested in:

Mysql string interception and obtaining data in the specified string
MySQL intercepts the sql statement of the string function
MySQL implements string concatenation, interception, replacement, and position search operations

<<: Vue realizes the whole process of slider drag verification function

>>: How to use the Linux nl command