SQL Server 函數：人民幣數字轉中文大寫金額

核心函數 dbo.f_num_chn

支援正負數、小數位（角分）、零元整等完整財務格式，精度達 14 位整數 + 2 位小數。

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 = 4 OR @i = 8 OR @i = 12 OR @i = 14)))
                SET @c_data = @c_data + SUBSTRING('零壹貳三肆伍陸柒捌玖', CAST(@n_str AS INT) + 1, 1);

            -- 處理單位字元
            IF NOT ((@n_str = '0') AND (@i <> 4) AND (@i <> 8) AND (@i <> 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

輔助函數 NToCC（單位數字轉中文）

CREATE FUNCTION NToCC (@n INT) RETURNS VARCHAR(2)
AS BEGIN
    DECLARE @s VARCHAR(2);
    IF @n = 0 SET @s = '零';
    IF @n = 1 SET @s = '壹';
    IF @n = 2 SET @s = '貳';
    IF @n = 3 SET @s = '叁';
    IF @n = 4 SET @s = '肆';
    IF @n = 5 SET @s = '伍';
    IF @n = 6 SET @s = '陸';
    IF @n = 7 SET @s = '柒';
    IF @n = 8 SET @s = '捌';
    IF @n = 9 SET @s = '玖';
    RETURN @s;
END

進階版 numbertoChineseBigString（支援億元級）

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
        IF @k = 9 SET @r = @r + dbo.NToCC(CONVERT(INT, SUBSTRING(REVERSE(@i), @k, 1))) + '億';
        IF @k = 8 SET @r = @r + dbo.NToCC(CONVERT(INT, SUBSTRING(REVERSE(@i), @k, 1))) + '千';
        IF @k = 7 SET @r = @r + dbo.NToCC(CONVERT(INT, SUBSTRING(REVERSE(@i), @k, 1))) + '佰';
        IF @k = 6 SET @r = @r + dbo.NToCC(CONVERT(INT, SUBSTRING(REVERSE(@i), @k, 1))) + '拾';
        IF @k = 5 SET @r = @r + dbo.NToCC(CONVERT(INT, SUBSTRING(REVERSE(@i), @k, 1))) + '萬';
        IF @k = 4 SET @r = @r + dbo.NToCC(CONVERT(INT, SUBSTRING(REVERSE(@i), @k, 1))) + '千';
        IF @k = 3 SET @r = @r + dbo.NToCC(CONVERT(INT, SUBSTRING(REVERSE(@i), @k, 1))) + '佰';
        IF @k = 2 SET @r = @r + dbo.NToCC(CONVERT(INT, SUBSTRING(REVERSE(@i), @k, 1))) + '拾';
        IF @k = 1 SET @r = @r + dbo.NToCC(CONVERT(INT, SUBSTRING(REVERSE(@i), @k, 1))) + '元';
        SET @k = @k - 1;
    END

    IF CONVERT(FLOAT, @f) = 0 SET @r = @r + '整';
    ELSE BEGIN
        IF SUBSTRING(@s, 1, 1) > '' SET @r = @r + dbo.NToCC(CONVERT(INT, SUBSTRING(@f, 1, 1))) + '角';
        IF SUBSTRING(@s, 2, 1) > '' SET @r = @r + dbo.NToCC(CONVERT(INT, SUBSTRING(@f, 2, 1))) + '分';
    END
    RETURN @r;
END

使用範例

SELECT dbo.f_num_chn(12345.67);  -- 輸出：壹萬貳仟叁佰肆拾伍圓陸角柒分
SELECT dbo.f_num_chn(-9876.50);  -- 輸出：(負數)玖仟捌佰柒拾陸圓伍角整
SELECT dbo.numbertoChineseBigString(100000000.00); -- 輸出：壹億元整

適用場景： 財務報表、支票列印、發票金額轉換、對帳單自動生成等需中文大寫金額的業務系統。