將數值金額 (如 1234.56) 轉換為中文大寫金額格式 (如 壹仟貳佰叁拾肆元伍角陸分),支援整數與小數位,適用於財務報表、列印支票等場景。
CREATE FUNCTION dbo.L2U (
@n_LowerMoney NUMERIC(15,2), -- 小寫金額,最長 15 位含 2 位小數
@v_TransType INT -- 0=讀法優化 (合併零), 1=直譯
)
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @v_LowerStr VARCHAR(200) -- 格式化後數字字串
DECLARE @v_UpperPart VARCHAR(200) -- 單位字元
DECLARE @v_UpperStr VARCHAR(200) -- 累積結果
DECLARE @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 '玖'
ELSE '' END
-- 單位對應 (位置索引對應中文數位單位)
SELECT @v_UpperPart = @v_UpperPart + 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 '萬'
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, '零分', '')
-- 移除前導零
IF LEFT(@v_UpperStr, 1) = '零' SET @v_UpperStr = SUBSTRING(@v_UpperStr, 2, 200)
END
RETURN @v_UpperStr
END
GO
| 輸入金額 | 呼叫方式 | 輸出結果 |
|---|---|---|
| 1234.56 | SELECT dbo.L2U(1234.56, 0) | 壹仟貳佰叁拾肆元伍角陸分 |
| 1000.00 | SELECT dbo.L2U(1000.00, 0) | 壹仟元整 |
| 0.05 | SELECT dbo.L2U(0.05, 0) | 伍分 |
| 1000000 | SELECT dbo.L2U(1000000, 0) | 壹佰萬元整 |