CREATE FUNCTION [dbo].[f_num_chn] (@num NUMERIC(14,5))
RETURNS VARCHAR(100) WITH ENCRYPTION
AS
BEGIN
DECLARE @n_data VARCHAR(20), @c_data VARCHAR(100), @n_str VARCHAR(10), @i INT
SET @n_data = RIGHT(SPACE(14) + CAST(CAST(ABS(@num*100) AS BIGINT) AS VARCHAR(20)), 14)
SET @c_data = ''
SET @i = 1
WHILE @i <= 14
BEGIN
SET @n_str = SUBSTRING(@n_data, @i, 1)
IF @n_str <> ' '
BEGIN
-- 零值抑制邏輯
IF NOT ((SUBSTRING(@n_data, @i, 2) = '00') OR
((@n_str = '0') AND (@i IN (4,8,12,14))))
SET @c_data = @c_data + SUBSTRING('零壹貳三肆伍陸柒捌玖', CAST(@n_str AS INT)+1, 1)
-- 單位添加
IF NOT ((@n_str = '0') AND (@i NOT IN (4,8,12)))
SET @c_data = @c_data + SUBSTRING('仟佰拾億仟佰拾萬仟佰拾圓角分', @i, 1)
-- 億萬修正
IF SUBSTRING(@c_data, LEN(@c_data)-1, 2) = '億萬'
SET @c_data = SUBSTRING(@c_data, 1, LEN(@c_data)-1)
END
SET @i = @i + 1
END
IF @num < 0 SET @c_data = '(負數)' + @c_data
IF @num = 0 SET @c_data = '零圓'
IF @n_str = '0' SET @c_data = @c_data + '整'
RETURN @c_data
END
將單位字元與數字轉換分離,邏輯更清晰,支援至億元級。
-- 單位數字轉中文
CREATE FUNCTION NToCC(@n INT) RETURNS VARCHAR(2) AS
BEGIN
DECLARE @s VARCHAR(2)
SELECT @s = CASE @n
WHEN 0 THEN '零' WHEN 1 THEN '壹' WHEN 2 THEN '貳' WHEN 3 THEN '三'
WHEN 4 THEN '肆' WHEN 5 THEN '伍' WHEN 6 THEN '陸' WHEN 7 THEN '柒'
WHEN 8 THEN '捌' WHEN 9 THEN '玖' ELSE '' END
RETURN @s
END
GO
-- 主轉換函數
CREATE FUNCTION numbertoChineseBigString(@m MONEY) RETURNS VARCHAR(255) AS
BEGIN
DECLARE @i VARCHAR(255), @f VARCHAR(255), @r VARCHAR(255), @s VARCHAR(255), @k INT
SET @s = CONVERT(VARCHAR(255), @m)
IF CHARINDEX('.', @s) > 0
BEGIN
SET @i = SUBSTRING(@s, 1, CHARINDEX('.', @s)-1)
SET @f = SUBSTRING(@s, CHARINDEX('.', @s)+1, LEN(@s))
END ELSE SET @i = @s
SET @r = ''; SET @k = LEN(@i)
WHILE @k >= 1
BEGIN
SET @r = @r + dbo.NToCC(CONVERT(INT, SUBSTRING(REVERSE(@i), @k, 1))) +
CASE @k
WHEN 9 THEN '億' WHEN 8 THEN '千' WHEN 7 THEN '佰' WHEN 6 THEN '拾'
WHEN 5 THEN '萬' WHEN 4 THEN '千' WHEN 3 THEN '佰' WHEN 2 THEN '拾'
WHEN 1 THEN '元' ELSE '' END
SET @k = @k - 1
END
-- 小數部分處理
IF CONVERT(FLOAT, @f) = 0 SET @r = @r + '整'
ELSE BEGIN
IF SUBSTRING(@f,1,1) > '0' SET @r = @r + dbo.NToCC(CONVERT(INT,SUBSTRING(@f,1,1))) + '角'
IF SUBSTRING(@f,2,1) > '0' SET @r = @r + dbo.NToCC(CONVERT(INT,SUBSTRING(@f,2,1))) + '分'
END
-- 零值合併清理
SET @r = REPLACE(@r, '零千零佰零拾', '零')
SET @r = REPLACE(@r, '零佰零拾', '零')
SET @r = REPLACE(@r, '零千零佰', '零')
SET @r = REPLACE(@r, '零拾', '零')
SET @r = REPLACE(@r, '零千', '零')
SET @r = REPLACE(@r, '零佰', '零')
SET @r = REPLACE(@r, '拾零萬', '拾萬')
RETURN @r
END
CREATE FUNCTION dbo.L2U(@n_LowerMoney NUMERIC(15,2), @v_TransType INT)
RETURNS VARCHAR(200) AS
BEGIN
DECLARE @v_LowerStr VARCHAR(200), @v_UpperPart VARCHAR(200), @v_UpperStr VARCHAR(200), @i_I INT
SET @v_LowerStr = LTRIM(RTRIM(ROUND(@n_LowerMoney,2)))
SET @i_I = 1; SET @v_UpperStr = ''
WHILE @i_I <= LEN(@v_LowerStr)
BEGIN
SELECT @v_UpperPart = CASE SUBSTRING(@v_LowerStr, LEN(@v_LowerStr)-@i_I+1, 1)
WHEN '.' THEN '元' WHEN '0' THEN '零' WHEN '1' THEN '壹' WHEN '2' THEN '貳'
WHEN '3' THEN '三' WHEN '4' THEN '肆' WHEN '5' THEN '伍' WHEN '6' THEN '陸'
WHEN '7' THEN '柒' WHEN '8' THEN '捌' WHEN '9' THEN '玖' END
+ CASE @i_I
WHEN 1 THEN '分' WHEN 2 THEN '角' WHEN 3 THEN '' WHEN 4 THEN ''
WHEN 5 THEN '拾' WHEN 6 THEN '佰' WHEN 7 THEN '仟' WHEN 8 THEN '萬'
WHEN 9 THEN '拾' WHEN 10 THEN '佰' WHEN 11 THEN '仟' WHEN 12 THEN '億'
WHEN 13 THEN '拾' WHEN 14 THEN '佰' WHEN 15 THEN '仟' WHEN 16 THEN '萬' ELSE '' END
SET @v_UpperStr = @v_UpperPart + @v_UpperStr
SET @i_I = @i_I + 1
END
IF @v_TransType = 0
BEGIN
SET @v_UpperStr = REPLACE(@v_UpperStr, '零拾', '零')
SET @v_UpperStr = REPLACE(@v_UpperStr, '零佰', '零')
SET @v_UpperStr = REPLACE(@v_UpperStr, '零仟', '零')
SET @v_UpperStr = REPLACE(@v_UpperStr, '零零零', '零')
SET @v_UpperStr = REPLACE(@v_UpperStr, '零零', '零')
SET @v_UpperStr = REPLACE(@v_UpperStr, '零角零分', '整')
SET @v_UpperStr = REPLACE(@v_UpperStr, '零分', '整')
SET @v_UpperStr = REPLACE(@v_UpperStr, '零角', '零')
SET @v_UpperStr = REPLACE(@v_UpperStr, '零億零萬零元', '億元')
SET @v_UpperStr = REPLACE(@v_UpperStr, '億零萬零元', '億元')
SET @v_UpperStr = REPLACE(@v_UpperStr, '零億零萬', '億')
SET @v_UpperStr = REPLACE(@v_UpperStr, '零萬零元', '萬元')
SET @v_UpperStr = REPLACE(@v_UpperStr, '萬零元', '萬元')
SET @v_UpperStr = REPLACE(@v_UpperStr, '零億', '億')
SET @v_UpperStr = REPLACE(@v_UpperStr, '零萬', '萬')
SET @v_UpperStr = REPLACE(@v_UpperStr, '零元', '元')
SET @v_UpperStr = REPLACE(@v_UpperStr, '零零', '零')
END
-- 處理壹元以下金額前導字元
IF '元' = SUBSTRING(@v_UpperStr,1,1) SET @v_UpperStr = SUBSTRING(@v_UpperStr,2,LEN(@v_UpperStr)-1)
IF '零' = SUBSTRING(@v_UpperStr,1,1) SET @v_UpperStr = SUBSTRING(@v_UpperStr,2,LEN(@v_UpperStr)-1)
IF '角' = SUBSTRING(@v_UpperStr,1,1) SET @v_UpperStr = SUBSTRING(@v_UpperStr,2,LEN(@v_UpperStr)-1)
RETURN @v_UpperStr
END
f_num_chn,經實戰驗證、加密部署SELECT dbo.f_num_chn(1234567.89) -- 壹佰貳拾叁萬肆仟伍佰陸拾柒圓捌角玖分
SELECT dbo.numbertoChineseBigString(100102) -- 壹拾萬零壹佰零貳元整
SELECT dbo.L2U(-500.5, 0) -- (負數)伍佰圓伍角整