--說明:

--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 = "萬仟佰拾億仟佰拾萬仟佰拾元角分厘毫"