mysql 字段中提取汉字，去除数字以及字母（亲测有效，配效果

转载地址http://www.bubuko.com/infodetail-3307767.html

字段中提取汉字，去除数字以及字母

DELIMITER $$
DROP FUNCTION IF EXISTS `Num_char_extract`$$
CREATE FUNCTION `Num_char_extract`(Varstring VARCHAR(100)CHARSET utf8, flag INT) RETURNS VARCHAR(50) CHARSET utf8
COMMENT '标识 0 提取数字 1 提取字母 2提取数字+字母 3 提取汉字'
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 '^[u0391-uFFE5]')
        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 ;

运用

 SELECT Num_char_extract(sName,3) FROM `TbTerminal`

截取前.png 提取后.png

提取中文字符中的数字并转为int

CAST(REPLACE(CONVERT(d.sName USING ASCII),'?','') AS SIGNED INTEGER)

运用

  SELECT CAST(REPLACE(CONVERT(sName USING ASCII),'?','') AS SIGNED INTEGER) AS orderNum  FROM `TbTerminal`

提取字段中的数字.png