MySql判断汉字、日期、数字的具体函数

DROP FUNCTION IF EXISTS fc_is_hanzi;

CREATE FUNCTION fc_is_hanzi(
p_str VARCHAR(1024)
)
  RETURNS int(11)
  NOT DETERMINISTIC
  SQL SECURITY DEFINER
  COMMENT '检查字符串是否为汉字'
BEGIN
/检查字符串是否为汉字 返回值1-汉字 0-非汉字/

  DECLARE _ret, i, other_t, l_acode INT DEFAULT 0;
  SET _ret = 0;
  SET i = 1;
  SET other_t = 0;
  SET l_acode = 0;
  WHILE i <= CHAR_LENGTH(p_str) DO
    SET l_acode = ASCII(SUBSTRING(p_str, i, 1));
    IF l_acode<124 or l_acode>254 THEN
      SET other_t = other_t + 1;
    END IF;
    SET i = i + 1;
  END WHILE;
  IF other_t = 0 THEN
    SET _ret = 1;
  ELSE
    SET _ret = 0;
  END IF;
  RETURN _ret;
END;

DROP FUNCTION IF EXISTS fc_ck_date;

CREATE FUNCTION fc_ck_date(
p_cont CHAR(32) 
)
  RETURNS tinyint(4)
  NOT DETERMINISTIC
  SQL SECURITY DEFINER
  COMMENT '判定日期格式是否正确'
BEGIN
/判定日期格式是否正确（返回值1-正确 0-错误）/
/输入值格式为yyyyMMdd 或 yyyy-MM-dd/

IF(SELECT DATE_FORMAT(p_cont,'%Y%m%d')) IS NULL THEN
  RETURN 0;
ELSE
  RETURN 1;
END IF;

END;

DROP FUNCTION IF EXISTS fc_is_num;

CREATE FUNCTION fc_is_num(
p_string VARCHAR(32) 
)
  RETURNS int(4)
  NOT DETERMINISTIC
  SQL SECURITY DEFINER
  COMMENT '检查字符串是否为纯数字'
BEGIN
/检查字符串是否为纯数字/
/返回值1-为纯数字 0-非纯数字/

   DECLARE iResult INT DEFAULT 0;
   SELECT p_string REGEXP '^[0-9]$' INTO iResult;
   IF iResult = 1 THEN
    RETURN 1;
   ELSE
     RETURN 0;
   END IF;
END;