mysql函数查询杨姓员工_mysql函数练习一

DELIMITER $$

CREATE FUNCTION `replace_number_to_zh`(original VARCHAR(1000))

RETURNS VARCHAR(1000)

DETERMINISTIC

BEGIN

DECLARE temp VARCHAR(1000);

DECLARE ch VARCHAR(1);

DECLARE i,j INT;

DECLARE nums VARCHAR(200);

DECLARE cns VARCHAR(200);

DECLARE nums_count INT;

SET nums = '0,1,2,3,4,5,6,7,8,9';

SET cns = '零,一,二,三,四,五,六,七,八,九';

SET nums_count = CHAR_LENGTH(nums)-CHAR_LENGTH(REPLACE(nums,',','')) + 1;

SET i = 1;

SET temp = '';

IF original REGEXP '[0-9]' THEN

loop_label: LOOP

IF i>CHAR_LENGTH(original) THEN

LEAVE loop_label;

END IF;

SET ch = SUBSTRING(original,i,1);

IF NOT ch REGEXP '[0-9]' THEN

IF ch = ' ' THEN

SET i=i+1;

ITERATE loop_label;

END IF;

SET temp = CONCAT(temp,ch);

ELSE

SET j = 1;

WHILE j <= nums_count DO

IF ch = SUBSTRING_INDEX(SUBSTRING_INDEX(nums, ',', j),',',-1) THEN

SET temp = CONCAT(temp,SUBSTRING_INDEX(SUBSTRING_INDEX(cns, ',', j),',',-1));

END IF;

SET j=j+1;

END WHILE;

END IF;

SET i=i+1;

END LOOP;

ELSE

SET temp = original;

END IF;

RETURN temp;

END$$

DELIMITER ;

说实话，以前真没写过sql函数。这次涉及到，就勉勉强强写了个，记录下便于以后参考。

该函数的功能就是去空格，单个阿拉伯数字换成中文数字。

主要涉及的点就是数组循环、正则匹配。