--说明:
--1.本函数范围从 毫 ~ 兆
--2.有四种精度(元,角 ,分,厘 ,毫)
--3.有三种进位规则(四舍五入,接舍去,非0就入)
--参数说明:dbo.MoneyToCapital( 数值 , 进位 , 精度)
--进位 (0 四舍五入, 1 直接舍去,2 非0就入)
--精确度 (0 元,1 角 ,2 分,3 厘 ,4 毫)
--------------------------------------------------------------------------------------------------------
--测试数据:
DECLARE @intNum decimal(38,4)
SET @intNum = 1123456780.2154
--SET @intNum = 1001
--SET @intNum = 100100
--SET @intNum = 1005001
--SET @intNum = 100.11
--SET @intNum = 100.00
--SET @intNum = 100.01
SET @intNum = 99999999999999.9999 -- 最大 <1百兆(精确到毫)
--SET @intNum = 10025.1234
--SET @intNum = 12345.6789
SELECT dbo.MoneyToCapital(@intNum,0,4)
--------------------------------------------------------------------------------------------------------
Go
CREATE FUNCTION MoneyToCapital
(
@mnyNumber decimal(38,4),
@intIsRound int = 0, -- 进位 (0 四舍五入, 1 直接舍去,2 非0就入)
@intPrecision int = 2 -- 精确度: 0 元,1 角 ,2 分,3 厘 ,4 毫
)
RETURNS nvarchar(50)
BEGIN
DECLARE @strReturn nvarchar(50)
DECLARE @strMoney varchar(50)
DECLARE @intLen int
DECLARE @strC1 char(1)
DECLARE @strC2 char(1)
DECLARE @strC3 char(1)
DECLARE @intJ int
DECLARE @necMoney decimal(38,4)
DECLARE @strMoneyUnit nvarchar(50)
DECLARE @strNumberCapital nvarchar(50)
SET @strMoneyUnit = '毫厘分角元拾佰仟万拾佰仟亿拾佰仟兆拾佰仟京拾佰仟'
SET @strNumberCapital = '零壹贰叁肆伍陆柒捌玖'
--0 的情况
IF @mnyNumber = 0 BEGIN
SET @strReturn = '零元整'
RETURN @strReturn
END
--超出范围 的情况
IF @mnyNumber < 0 OR @mnyNumber > 99999999999999.9999 BEGIN RETURN CAST(@mnyNumber AS varchar(50))
END
------进位 超出范围
IF @intIsRound<0 OR @intIsRound>2 BEGIN
SET @intIsRound = 0
END
------精确度 超出范围
IF @intPrecision<0 OR @intPrecision>4 BEGIN
SET @intPrecision = 2
END
IF @intIsRound =1 BEGIN--直接舍去 SET @mnyNumber = ROUND(@mnyNumber,2,1)
END
ELSE IF @intIsRound = 2 BEGIN--非0就入 SET @mnyNumber = ROUND(@mnyNumber,2)
END
ELSE BEGIN--四舍五入 SET @mnyNumber = ROUND(@mnyNumber,@intPrecision)
END SET @necMoney = @mnyNumber * POWER(10,@intPrecision) --精确度 @intPrecision
SET @strMoney = CAST(CAST(@necMoney AS bigint) AS varchar(50))
SET @intLen = LEN(@strMoney) --长度
SET @strMoney = REVERSE(@strMoney) --逆转
SET @strReturn='' SET @intJ = 1 -- @intPrecision 精确度: 0 元,1 角 ,2 分,3 厘 ,4 毫( 1 开始对应 毫)
WHILE @intJ <= @intLen BEGIN SET @strC1 = SUBSTRING(@strMoney,@intJ-1,1)
SET @strC2 = SUBSTRING(@strMoney,@intJ,1)
SET @strC3 = SUBSTRING(@strMoney,@intJ+1,1) SET @strReturn = SUBSTRING(@strNumberCapital , CAST(@strC2 AS int)+1 , 1) +SUBSTRING(@strMoneyUnit , @intJ+4-@intPrecision , 1) + @strReturn
SET @intJ = @intJ + 1
END --替换多余的部分
set @returnStr = replace(@returnStr,'零亿','零')
set @returnStr = replace(@returnStr,'零千','零')
set @returnStr = replace(@returnStr,'零百','零')
set @returnStr = replace(@returnStr,'零拾','零')
set @returnStr = replace(@returnStr,'零零零','零')
set @returnStr = replace(@returnStr,'零零','零')
set @returnStr = replace(@returnStr,'零万','万')
set @returnStr = replace(@returnStr,'零元','元')
set @returnStr = replace(@returnStr,'零角','零')
set @returnStr = replace(@returnStr,'零分','') while left(@returnStr,1) = '零'
Begin
set @returnStr = right(@returnStr,len(@returnStr)-1)
end
if ((left(@returnStr,1)='万'))
begin
set @returnStr = right(@returnStr,len(@returnStr)-1)
end
if ((left(@returnStr,1)='元'))
begin
set @returnStr = right(@returnStr,len(@returnStr)-1)
end
while left(@returnStr,1) = '零'
Begin
set @returnStr = right(@returnStr,len(@returnStr)-1)
end
while right(@returnStr,1) = '零'
Begin
set @returnStr = left(@returnStr,len(@returnStr)-1)
end set @returnStr = replace(@returnStr,'亿万','亿零')
set @returnStr = replace(@returnStr,'零元','元')
set @returnStr = replace(@returnStr,'零零零','零')
set @returnStr = replace(@returnStr,'零零','零') if (@returnStr='')
begin
set @returnStr = '零元'
end
if ((right(@returnStr,1)='元'))
begin
set @returnStr = @returnStr + '整'
end RETURN @strReturn
END --String1 = "零壹贰叁肆伍陆柒捌玖"
--String2 = "万仟佰拾亿仟佰拾万仟佰拾元角分厘毫"