迫不得已才出此下策。建议系统在设计时不要这么设计!!!!
功能场景:数据库中有备注字段,取出要求4的数据,备注字段的数据如下图:
1、要求1:xxxxxx;
2、要求2:xxxxx
3、要求3:xxxxxx
4、要求4:xxxxxxxxx
5、要求5:xxxxxxxxx
6、要求6:xxxxxxxxxxxxx
7、要求7
自定义mysql函数:
CREATE DEFINER=`root`@`%` FUNCTION `ItemNo`(`orderid` VARCHAR(2000)) RETURNS varchar(2000) CHARSET utf8 COLLATE utf8_unicode_ci
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE v_notice_id varchar(255);
DECLARE para varchar(255);
DECLARE cur1 CURSOR FOR SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(orderid,'\n',help_topic_id+1),'\n',-1) AS para
FROM
mysql.help_topic
WHERE
help_topic_id < LENGTH(orderid)-LENGTH(REPLACE(orderid,'\n',''))+1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
the_loop: LOOP
FETCH cur1 INTO v_notice_id;
IF done THEN
LEAVE the_loop;
END IF;
if locate('要求4',v_notice_id)<>0 then
SELECT SUBSTRING(v_notice_id, locate(':',v_notice_id)+1,LENGTH(v_notice_id)+1 ) into para;
end if;
END LOOP the_loop;
CLOSE cur1;
RETURN para;
END