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
BEGINDECLARE len INT DEFAULT 0;DECLARE Tmp VARCHAR(100) DEFAULT '';SET len=CHAR_LENGTH(Varstring);IF flag = 0 THENWHILE len > 0 DOIF MID(Varstring,len,1)REGEXP'[0-9]' THENSET Tmp=CONCAT(Tmp,MID(Varstring,len,1));END IF;SET len = len - 1;END WHILE;ELSEIF flag=1THENWHILE len > 0 DOIF (MID(Varstring,len,1)REGEXP '[a-zA-Z]') THENSET Tmp=CONCAT(Tmp,MID(Varstring,len,1));END IF;SET len = len - 1;END WHILE;ELSEIF flag=2THENWHILE len > 0 DOIF ( (MID(Varstring,len,1)REGEXP'[0-9]')OR (MID(Varstring,len,1)REGEXP '[a-zA-Z]') ) THENSET Tmp=CONCAT(Tmp,MID(Varstring,len,1));END IF;SET len = len - 1;END WHILE;ELSEIF flag=3THENWHILE len > 0 DOIF not (MID(Varstring,len,1)REGEXP '^[u4e00-u9fa5]')and not (MID(Varstring,len,1)REGEXP '^[/]')and not (MID(Varstring,len,1)REGEXP '^[ ]')THENSET Tmp=CONCAT(Tmp,MID(Varstring,len,1));END IF;SET len = len - 1;END WHILE;ELSESET 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. / ↩︎