Mysql User-defined functions implement string matching _ MySQL

Mysql User-defined functions implement string matching bitsCN.com

Mysql User-defined functions implement string matching

First:

The first parameter of the fSearch function is a single string (that is, no special string is separated)

The first parameter of the fSearch function is a non-single string.

Multiple strings can also be matched.

Function code:

01DELIMITER $$02Create   function fSearch(targetStr VARCHAR(100),findStr VARCHAR(100)) RETURNS INT03BEGIN04DECLARE strNum INT;05DECLARE cIndex INT DEFAULT 1;06DECLARE cStr VARCHAR(50);07DECLARE flag INT;08set strNum=1+(length(targetStr) - length(replace(targetStr,',','')));09WHILE cIndex<=strNum10DO11set cStr=reverse(substring_index(reverse(substring_index(targetStr,',',cIndex)),',',1));12set flag=LOCATE(cStr,findStr);13if (flag>0&&cStr!='') THEN14RETURN flag;15END IF;16set cIndex=cIndex+1;17END WHILE;18RETURN 0;19END $$20DELIMITER ;

Use cases:

Add a field to the blog system to store the tag of the article. assume that the tag is 'Java, php, asp, web developer ', if you want to use this tag to find articles with similar tags, you can:

Select * from blog B where fSearch ('Java, php, asp, web developer', B. tags)> 0

BitsCN.com