-- 如果函数已存在则删除
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) -- 应返回: 壹佰贰拾叁元肆角伍分
