mysql如何搜索不连续的字符_如何从MySQL中的一串文本中选择不连续的字符？

这个功能做你想要的：

CREATE DEFINER = `root`@`localhost` FUNCTION `test`.`getsubset`(selection mediumtext, longstring mediumtext)

RETURNS varchar(200)

LANGUAGE SQL

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT 'This function returns a subset of characters.'

BEGIN

SET @res:='';

SET @selection:=selection;

WHILE @selection<>'' DO

set @pos:=CONVERT(@selection, signed);

set @res := concat_ws('',@res,SUBSTRING(longstring,@pos,1));

IF LOCATE(',',@selection)=0 THEN

SET @selection:='';

END IF;

set @selection:=SUBSTRING(@selection,LOCATE(',',@selection)+1);

END WHILE;

RETURN @res;

END

注意：CONVERT(‘1,2,3,4’,已签名)将产生1,但它会发出警告.

我将它定义为可在数据库测试中使用.

该函数有两个参数;带有位置列表的字符串(！),以及从中获取字符的长字符串.

使用此示例：

mysql> select * from keepdiscard;

+---------+------------+

| charind | keepordisc |

+---------+------------+

| 1 | keep |

| 2 | discard |

| 3 | keep |

| 4 | discard |

| 5 | keep |

| 6 | keep |

+---------+------------+

6 rows in set (0.00 sec)

mysql> select * from test;

+-------------------+

| longstring |

+-------------------+

| abcdefghijklmnopq |

| 123456789 |

+-------------------+

2 rows in set (0.00 sec)

mysql> select getsubset(group_concat(charind ORDER BY charind),longstring) as result from keepdiscard, test where keepordisc='keep' group by longstring;

+--------+

| result |

+--------+

| 1356 |

| acef |

+--------+

2 rows in set, 6 warnings (0.00 sec)

警告源于在函数中快速转换为整数. (见上面的评论)