Functions for MySql to judge Chinese characters, dates, and numbers

Several common mysql Functions

/*************************************** **************
1. Determine whether the string is returned from Chinese characters: 1-0-non-Chinese Characters

**************************************** *************/

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 'check whether the string is a Chinese characters'
BEGIN
/* Check whether the string is returned from Chinese characters: 1-0-non-Chinese characters */

DECLARE _ ret, I, other_cnt, l_acode INT DEFAULT 0;
SET _ ret = 0;
SET I = 1;
SET other_cnt = 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_cnt = other_cnt + 1;
End if;
SET I = I + 1;
End while;
IF other_cnt = 0 THEN
SET _ ret = 1;
ELSE
SET _ ret = 0;
End if;
RETURN _ ret;
END;

/*************************************** **************
2. Determine whether the date format is correct (Return Value: 1-correct 0-error)

**************************************** *************/

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 'determine whether the date format is correct'
BEGIN
/* Determine whether the date format is correct (Return Value: 1-correct 0-error )*/
/* The input value format is yyyyMMdd or yyyy-MM-dd */

IF (SELECT DATE_FORMAT (p_cont, '% Y % m % D') IS NULL THEN
RETURN 0;
ELSE
RETURN 1;
End if;

END;

/*************************************** **************
3. Determine whether the string is a pure number (Return Value: 1-0-non-pure number)

**************************************** *************/

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 'check whether the string is a pure number'
BEGIN
/* Check whether the string is a pure number */
/* Return value: 1-only digit 0-not pure digit */

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;