- # 使用 select id,nickname,replace_emoji(nickname) as f2 from emoji
- DROP FUNCTION IF EXISTS replace_emoji;
- CREATE FUNCTION replace_emoji (nickname VARCHAR(255))
- RETURNS VARCHAR(255)
- BEGIN
- # 替换微信表情, <span class="emoji emojixxx"></span>
- DECLARE l_span INT(11);
- DECLARE r_span INT(11);
- DECLARE result VARCHAR(255);
- DECLARE _replace VARCHAR(255);
- SET result = nickname;
- SET r_span = 1;
- SET l_span = locate("<span", result);
- WHILE (l_span > 0) DO
- SET r_span = LOCATE('></span>', result, l_span);
- IF (r_span > 0) THEN
- SET _replace = SUBSTRING(result, l_span, r_span - l_span + 8);
- SET result = REPLACE(result, _replace, '');
- ELSE
- RETURN result;
- END IF;
- SET l_span = LOCATE('<span', result);
- END WHILE;
- RETURN result;
- END
- --该片段来自于http://www.codesnippet.cn/detail/1706201614743.html
来源: http://www.codesnippet.cn/detail/1706201614743.html