--說明:
--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 = "萬仟佰拾億仟佰拾萬仟佰拾元角分厘毫"
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
