MYSQL's function of judging Chinese characters, dates and numbers

Several flat MySQL functions commonly used

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

1. Judge whether the string is a Chinese character return value: 1-Chinese characters 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 if string is Kanji '

BEGIN

/* Check whether the string is a Chinese character return value: 1-Kanji 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 if 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 ' decision date format is correct '

BEGIN

/* Determine date format is correct (return value: 1-correct 0-error) * *

/* 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 if the string is a pure number (return value: 1-Pure number 0-not 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 if the string is a pure number '

BEGIN

/* Check whether the string is a pure number * *

/* Return value: 1-pure digit 0-Non-pure number * *

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;