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函数。这次涉及到,就勉勉强强写了个,记录下便于以后参考。
该函数的功能就是去空格,单个阿拉伯数字换成中文数字。
主要涉及的点就是数组循环、正则匹配。