MySQL低版本中：字符串中的数字、英文字符、汉字提取

我们如何提醒一个字段中的汉字和数字呢

高版本指mysql8.0以上

使用sql语句

SELECT REGEXP_REPLACE(column_name, '[^\\p{Han}]', '') AS chinese_characters
FROM table_name;

 其中  column_name指名称列，table_name是表名 

2.低版本使用

需要新建函数

DELIMITER $$
 
DROP FUNCTION IF EXISTS `num_char_extract`$$
 
CREATE DEFINER=`root`@`%` FUNCTION `num_char_extract`(Varstring VARCHAR(100)CHARSET utf8, flag INT) RETURNS varchar(50) CHARSET utf8
BEGIN
    DECLARE len INT DEFAULT 0;
    DECLARE Tmp VARCHAR(100) DEFAULT '';
    SET len=CHAR_LENGTH(Varstring);
    IF flag = 0 
    THEN
        WHILE len > 0 DO
        IF MID(Varstring,len,1)REGEXP'[0-9]' THEN
        SET Tmp=CONCAT(Tmp,MID(Varstring,len,1));
        END IF;
        SET len = len - 1;
        END WHILE;
    ELSEIF flag=1
    THEN
        WHILE len > 0 DO
        IF (MID(Varstring,len,1)REGEXP '[a-zA-Z]') 
        THEN
        SET Tmp=CONCAT(Tmp,MID(Varstring,len,1));
        END IF;
        SET len = len - 1;
        END WHILE;
    ELSEIF flag=2
    THEN
        WHILE len > 0 DO
        IF ( (MID(Varstring,len,1)REGEXP'[0-9]')
        OR (MID(Varstring,len,1)REGEXP '[a-zA-Z]') ) 
        THEN
        SET Tmp=CONCAT(Tmp,MID(Varstring,len,1));
        END IF;
        SET len = len - 1;
        END WHILE;
    ELSEIF flag=3
    THEN
        WHILE len > 0 DO
        IF not (MID(Varstring,len,1)REGEXP '^[u4e00-u9fa5]')
				and not (MID(Varstring,len,1)REGEXP '^[/]')
		and not (MID(Varstring,len,1)REGEXP '^[ ]')
		
        THEN
        SET Tmp=CONCAT(Tmp,MID(Varstring,len,1));
        END IF;
        SET len = len - 1;
        END WHILE;
    ELSE
        SET Tmp = 'Error: The second paramter should be in (0,1,2,3)';
        RETURN Tmp;
    END IF;
    RETURN REVERSE(Tmp);
    END$$
DELIMITER ;

优化了除去斜杠和空格，如果需要展示的话，在第三个判断中去除’¹'和【】判断就可以实现

测试数据建立

测试使用

传值为1时候过滤字符

SELECT markinfo, num_char_extract(markinfo,1) as chinesechar from dev_move 

值为2时候过滤数字加字符

SELECT markinfo, num_char_extract(markinfo,2) as chinesechar from dev_move 

值为3过滤汉字

SELECT markinfo, num_char_extract(markinfo,3) as chinesechar from dev_move 

---

1. / ↩︎