金額轉中文大寫標量函數：SELECT 直接呼叫、可內嵌查詢

函數代碼

CREATE FUNCTION ChangeBigSmall(@ChangeMoney MONEY)
RETURNS VARCHAR(100)
AS
BEGIN
    DECLARE @StrNum CHAR(20) = '零壹貳參肆伍陸柒捌玖'
    DECLARE @StrUnit CHAR(30) = '萬仟佰拾億仟佰拾萬仟佰拾元角分'
    DECLARE @NumStr VARCHAR(100) = CAST(@ChangeMoney * 100 AS INT)
    DECLARE @Len INT = LEN(@NumStr)
    DECLARE @UnitStr VARCHAR(30) = RIGHT(@StrUnit, @Len)
    DECLARE @Result VARCHAR(100) = ''
    DECLARE @i INT = 1, @ZeroCount INT = 0

    WHILE @i <= @Len
    BEGIN
        DECLARE @Digit CHAR(1) = SUBSTRING(@NumStr, @i, 1)
        IF @Digit <> '0'
        BEGIN
            SET @Result = @Result + SUBSTRING(@StrNum, CAST(@Digit AS INT) + 1, 1) + SUBSTRING(@UnitStr, @i, 1)
            SET @ZeroCount = 0
        END
        ELSE
        BEGIN
            IF @ZeroCount = 0 OR @i = @Len - 9 OR @i = @Len - 5 OR @i = @Len - 1
                SET @Result = @Result + '零'
            SET @ZeroCount = @ZeroCount + 1
            IF @i = @Len - 10 BEGIN SET @Result = @Result + '億'; SET @ZeroCount = 0 END
            IF @i = @Len - 6  BEGIN SET @Result = @Result + '萬'; SET @ZeroCount = 0 END
            IF @i = @Len - 2  BEGIN SET @Result = @Result + '元'; SET @ZeroCount = 0 END
            IF @i = @Len      BEGIN SET @ZeroCount = 0 END
        END
        SET @i = @i + 1
    END
    RETURN @Result
END

使用範例

-- 直接在 SELECT 中呼叫
SELECT dbo.ChangeBigSmall(Amount) AS ChineseAmount FROM Orders

-- 參數化
DECLARE @Amt MONEY = 12345.67
SELECT dbo.ChangeBigSmall(@Amt)  -- 壹萬貳仟叁佰肆拾伍元陸角柒分

函數 vs 預存程序

效能提示

• 標量函數逐列執行，大結果集 (百萬列) 會有明顯 CPU 開銷。
• 高頻報表建議：預先在表中儲存轉換結果，或用計算欄位 (PERSISTED)。
• SQL Server 2019+ 可用 INLINE 標量函數 (自動內聯) 大幅提升效能。