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'零') BEGINSET @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;

可以在保留两位小数的前提下转换