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

Source: Internet
Author: User
Tags mysql functions

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;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.