SQLSEVER 实现货币数字转中文汉字

SQLSEVER 实现数字转换成中文（货币）

-- =============================================
-- Author:		LearnerPing
-- Create date: 2024/10/18
-- Description:	Change Number to Chinese
-- =============================================
Create FUNCTION GetNumberToChi
(	
	@p_amt NUMERIC(19,6)
)
RETURNS NVARCHAR(200) --返回返回值的数据类型
AS
BEGIN 
    DECLARE @p_amt_str NUMERIC(19,2)
    DECLARE @returnStr NVARCHAR(200);

    -- 如果金额大于等于1亿，则返回 '##########'
    IF (@p_amt >= 1000000000) 	
	        SET @returnStr = N'##########';
	ELSE
        -- 否则，返回 '00000000000'
        SET @returnStr = N'00000000000';

    SET @p_amt_str = ROUND(@p_amt,2)
    -- 将金额转换为12位字符串，并右对齐
    SET @returnStr = RIGHT(@returnStr + CAST(ROUND(@p_amt_str, 11, 2) AS NVARCHAR(12)), 12);

    -- 将数字替换为中文数字
    SET @returnStr = REPLACE(@returnStr, N'0', N'零');
    SET @returnStr = REPLACE(@returnStr, N'1', N'壹');
    SET @returnStr = REPLACE(@returnStr, N'2', N'贰');
    SET @returnStr = REPLACE(@returnStr, N'3', N'叁');
    SET @returnStr = REPLACE(@returnStr, N'4', N'肆');
    SET @returnStr = REPLACE(@returnStr, N'5', N'伍');
    SET @returnStr = REPLACE(@returnStr, N'6', N'陆');
    SET @returnStr = REPLACE(@returnStr, N'7', N'柒');
    SET @returnStr = REPLACE(@returnStr, N'8', N'捌');
    SET @returnStr = REPLACE(@returnStr, N'9', N'玖');

    -- 添加单位
    SET @returnStr = @returnStr + N'分';
    SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 1, 0, N'角');
    SET @returnStr = REPLACE(@returnStr, N'.', N'元');

    -- 添加金额单位
    SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 5, 0, N'拾');
    SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 7, 0, N'佰');
    SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 9, 0, N'仟');
    SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 11, 0, N'万');
    SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 13, 0, N'拾');
    SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 15, 0, N'佰');
    SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 17, 0, N'仟');
    SET @returnStr = STUFF(@returnStr, LEN(@returnStr) - 19, 0, N'亿');

    -- 替换零和单位
    SET @returnStr = REPLACE(@returnStr, N'零亿', N'零');
    SET @returnStr = REPLACE(@returnStr, N'零仟', N'零');
    SET @returnStr = REPLACE(@returnStr, N'零佰', N'零');
    SET @returnStr = REPLACE(@returnStr, N'零拾', N'零');
    SET @returnStr = REPLACE(@returnStr, N'零零零', N'零');
    SET @returnStr = REPLACE(@returnStr, N'零零', N'零');
    SET @returnStr = REPLACE(@returnStr, N'零万', N'万');
    SET @returnStr = REPLACE(@returnStr, N'零元', N'元');
    SET @returnStr = REPLACE(@returnStr, N'零角', N'零');
    SET @returnStr = REPLACE(@returnStr, N'零分', N'');

    -- 替换壹拾和佰拾
    SET @returnStr = REPLACE(@returnStr, N'壹拾', N'拾');
    SET @returnStr = REPLACE(@returnStr, N'佰拾', N'佰壹拾');

    -- 去除多余的零
    WHILE (LEFT(@returnStr, 1) = N'零') 
	BEGIN
        SET @returnStr = RIGHT(@returnStr, LEN(@returnStr) - 1);
    END 

    IF (LEFT(@returnStr, 1) = N'万') 
        SET @returnStr = RIGHT(@returnStr, LEN(@returnStr) - 1);
     

    IF (LEFT(@returnStr, 1) = N'元') 
        SET @returnStr = RIGHT(@returnStr, LEN(@returnStr) - 1);
     

    WHILE (LEFT(@returnStr, 1) = N'零') 
	BEGIN	
        SET @returnStr = RIGHT(@returnStr, LEN(@returnStr) - 1);
    END 

    WHILE (RIGHT(@returnStr, 1) = N'零') 
	BEGIN	
        SET @returnStr = LEFT(@returnStr, LEN(@returnStr) - 1);
    END 

    -- 替换亿万和零元
    SET @returnStr = REPLACE(@returnStr, N'亿万', N'亿零');
    SET @returnStr = REPLACE(@returnStr, N'零元', N'元');

    -- 如果返回字符串为空，则返回 '零元'
    IF (@returnStr = N'') 
        SET @returnStr = N'零元';
    

    -- 如果以元结尾，则添加 '正'
    IF (RIGHT(@returnStr, 1) = N'元') 
        SET @returnStr = @returnStr + N'正';
   
    RETURN @returnStr;
END;