MySQL:如何从字符串中删除所有单个字符? [英] MySQL: how to remove all single characters from a string?

问题描述

在MySQL中，我需要从字符串中删除所有单个字符:

In MySQL I need to remove all single characters from a string:

"A Quick Brown B C D Fox"-> "Quick Brown Fox"

有什么建议吗?

推荐答案

您希望[[:<:]]和[[:>:]]在MySQL中搜索单词边界. (在其他正则表达式，PERL，grep等中为\b.

You want [[:<:]] and [[:>:]] to search for word boundries in MySQL. (It would be \b in other regular expressions, PERL, grep, etc.)

所以...

'[[:<:]][a-zA-Z0-9][[:>:]]'

将找到任何单个字符.

确定，请尝试...

这是基于上面的功能，但经过硬编码以查找单个字符.我并不认为这仅仅是骇客.但是，如果您需要完成工作，并且无论出于何种原因都无法安装外部库，则不会降低没有任何单个字符可替换的字符串的速度:

This is based upon the function above, but hardcoded to look for single characters. I don't claim this to be much more than a hack. But if you need to get the job done, and you cant install the external libraries for whatever reason, it won't slow you down on strings that do NOT have any single characters to replace:

delimiter //
DROP FUNCTION IF EXISTS `REMOVE_SINGLE_CHARS`//
CREATE FUNCTION `REMOVE_SINGLE_CHARS`(original varchar(1000)) RETURNS varchar(1000) CHARSET utf8 DETERMINISTIC
BEGIN

DECLARE rxChars VARCHAR(20);
DECLARE temp VARCHAR(1000);
DECLARE ch1 CHAR(1);
DECLARE ch2 CHAR(1);
DECLARE ch3 CHAR(1);
DECLARE i INT;

SET rxChars = '[a-zA-Z0-9]';
SET i = 1;
SET temp = "";

IF original REGEXP CONCAT('[[:<:]]',rxChars,'[[:>:]]') THEN
  loop_label: LOOP
    set ch1 = SUBSTRING(original,i,1);
    if CHAR_LENGTH(original) > i THEN 
      set ch2 = SUBSTRING(original,i+1,1);
    END IF;
    if CHAR_LENGTH(original) > i+1 THEN 
      set ch3 = SUBSTRING(original,i+2,1);
    END IF;
    if(i = 1) THEN
        IF (ch1 NOT regexp rxChars OR ch2 regexp rxChars) THEN
          set temp = CONCAT(temp, ch1);
        END IF;
    END IF;

    IF(ch2 = ' ') THEN
      # Theoretically this is redundant, but for some reason the
      # CONCAT(temp, ch2) below is not working when ch2 = ' '   YMMV
      set temp = CONCAT(temp, ' ');
    ELSE 
      IF(ch2 NOT regexp rxChars OR ch1 regexp rxChars OR (i+1<CHAR_LENGTH(original) AND ch3 regexp rxChars)) THEN
        set temp = CONCAT(temp, ch2);
      END IF;
    END IF;

    IF i+2>CHAR_LENGTH(original) THEN
      LEAVE loop_label;
    END IF;
    SET i=i+1;
  END LOOP;
ELSE
  SET temp = original;
END IF;
RETURN temp;

END//

这篇关于MySQL:如何从字符串中删除所有单个字符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！