萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 人民幣數字轉中文大寫函數:三種實現方案完整代碼

方案一:標準財務級函數 f_num_chn(支援負數、角分、整字處理)

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

方案二:模組化拆解版(NToCC + numbertoChineseBigString)

將單位字元與數字轉換分離,邏輯更清晰,支援至億元級。

-- 單位數字轉中文
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

方案三:通用迴圈法 L2U(支援正負、可配置輸出模式)

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)           -- (負數)伍佰圓伍角整
copyright © 萬盛學電腦網 all rights reserved