-- 如果函數已存在則刪除
IF OBJECT_ID('dbo.ConvertToRMBChineseNew', 'FN') IS NOT NULLDROP FUNCTION dbo.ConvertToRMBChineseNew
GOCREATE FUNCTION dbo.ConvertToRMBChineseNew
(@NumberInput SQL_VARIANT -- 使用 SQL_VARIANT 兼容數字和字符串輸入
)
RETURNS NVARCHAR(200)
AS
BEGINDECLARE @Result NVARCHAR(200)DECLARE @NumStr NVARCHAR(50)DECLARE @IntegerPart NVARCHAR(20)DECLARE @DecimalPart NVARCHAR(2)DECLARE @TempStr NVARCHAR(20)DECLARE @Index INTDECLARE @Len INTDECLARE @Char NCHAR(1)DECLARE @UnitIndex INTDECLARE @ZeroFlag BIT-- 初始化結果SET @Result = N''SET @IntegerPart = N''SET @DecimalPart = N''SET @ZeroFlag = 0-- 定義大寫數字和單位DECLARE @Digits TABLE (Digit INT, Char NVARCHAR(1))INSERT INTO @Digits VALUES (0, N'零'), (1, N'壹'), (2, N'貳'), (3, N'叁'), (4, N'肆'), (5, N'伍'), (6, N'陸'), (7, N'柒'), (8, N'捌'), (9, N'玖')DECLARE @Units TABLE (Position INT, Unit NVARCHAR(2))INSERT INTO @Units VALUES (1, N''), (2, N'拾'), (3, N'佰'), (4, N'仟'), (5, N'萬'), (6, N'拾'), (7, N'佰'), (8, N'仟'), (9, N'億'), (10, N'拾'), (11, N'佰'), (12, N'仟')-- 將輸入轉換為字符串SET @NumStr = LTRIM(RTRIM(CAST(@NumberInput AS NVARCHAR(50))))-- 處理空值或非數字IF @NumStr IS NULL OR @NumStr = '' OR ISNUMERIC(@NumStr) = 0RETURN N'無效輸入'-- 轉換為絕對值并格式化DECLARE @NumberValue DECIMAL(18,6)SET @NumberValue = ABS(CAST(@NumStr AS DECIMAL(18,6)))-- 分離整數和小數部分 - 修正關鍵點SET @IntegerPart = CAST(CAST(@NumberValue AS BIGINT) AS NVARCHAR(20))-- **關鍵修正：正確提取角分**-- 計算小數部分的角(第一位)和分(第二位)DECLARE @JiaoValue INT, @FenValue INTSET @JiaoValue = CAST((@NumberValue - CAST(@NumberValue AS BIGINT)) * 10 AS INT) -- 得到角SET @FenValue = CAST(((@NumberValue - CAST(@NumberValue AS BIGINT)) * 100) AS INT) % 10 -- 得到分-- 將角分轉換為字符串用于后續邏輯判斷（如果需要）SET @DecimalPart = RIGHT('0' + CAST(@JiaoValue AS NVARCHAR(1)), 1) + RIGHT('0' + CAST(@FenValue AS NVARCHAR(1)), 1)-- 注意：@DecimalPart 現在是 '角分' 兩位，如 '60' for 0.6-- 處理整數部分 (保持不變)SET @Len = LEN(@IntegerPart)SET @Index = 1WHILE @Index <= @LenBEGINSET @Char = SUBSTRING(@IntegerPart, @Index, 1)SET @UnitIndex = @Len - @Index + 1SELECT @TempStr = Char FROM @Digits WHERE Digit = CAST(@Char AS INT)IF @Char != '0'BEGINSET @Result = @Result + @TempStrIF @UnitIndex IN (5, 9) OR @TempStr != N'零'SET @Result = @Result + (SELECT Unit FROM @Units WHERE Position = @UnitIndex)SET @ZeroFlag = 0ENDELSEBEGINIF @ZeroFlag = 0BEGINIF @UnitIndex IN (5, 9)SET @Result = @Result + N'零' + (SELECT Unit FROM @Units WHERE Position = @UnitIndex)ELSESET @Result = @Result + N'零'SET @ZeroFlag = 1ENDENDSET @Index = @Index + 1END-- 清理多余的"零"WHILE RIGHT(@Result, 1) = N'零' AND LEN(@Result) > 1 AND LEFT(RIGHT(@Result, 2), 1) != N'元'BEGINSET @Result = LEFT(@Result, LEN(@Result) - 1)ENDSET @Result = REPLACE(@Result, N'零零', N'零')SET @Result = REPLACE(@Result, N'零零', N'零')-- 添加"元"SET @Result = @Result + N'元'-- **關鍵修正：處理小數部分 (角和分)**DECLARE @Jiao NCHAR(1), @Fen NCHAR(1)SET @Jiao = SUBSTRING(@DecimalPart, 1, 1) -- 第一位是角SET @Fen = SUBSTRING(@DecimalPart, 2, 1) -- 第二位是分-- 處理角IF @Jiao != '0'BEGINSELECT @TempStr = Char FROM @Digits WHERE Digit = CAST(@Jiao AS INT)SET @Result = @Result + @TempStr + N'角'ENDELSE IF @Fen != '0' -- 角為0但分不為0，需要加"零"BEGINSET @Result = @Result + N'零'END-- 處理分IF @Fen != '0'BEGINSELECT @TempStr = Char FROM @Digits WHERE Digit = CAST(@Fen AS INT)SET @Result = @Result + @TempStr + N'分'END-- **關鍵修正：只有當角和分都為0時才加"整"**IF @Jiao = '0' AND @Fen = '0'SET @Result = @Result + N'整'-- 清理結果SET @Result = REPLACE(@Result, N'元零整', N'元整')RETURN @Result
END
GO-- 使用示例
-- SELECT dbo.fn_NumberToRMB(6849.6) -- 應返回: 陸仟捌佰肆拾玖元陸角
-- SELECT dbo.fn_NumberToRMB('6849.6') -- 應返回: 陸仟捌佰肆拾玖元陸角
-- SELECT dbo.fn_NumberToRMB(1001) -- 應返回: 壹仟零壹元整
-- SELECT dbo.fn_NumberToRMB(0.05) -- 應返回: 零元伍分
-- SELECT dbo.fn_NumberToRMB(123.45) -- 應返回: 壹佰貳拾叁元肆角伍分
