Create function [dbo].[Fn_getformatmoney](@moneyNumeric -,2))returns nvarchar( +) asbegin Declare @money_num nvarchar( -)--the character form in which the amount is stored,@money_chn nvarchar( +)--to store the amount in Chinese capital form,@n_chn nvarchar(1),@i int --Temp Variable Select @money_chn= Case when @money>=0 Then "' Else NULL End , @money=ABS(@money) , @money_num=Stuff(Str(@money, the,2), -,1,"')--Add the front space to get it in place (minus the decimal point),@i=Patindex('%[1-9]%',@money_num)--find the highest bit of money while @i>=1 and @i<= - begin Set @n_chn=substring(@money_num,@i,1) if @n_chn<>'0' or(substring(@money_num,@i+1,1)<>'0' and @i not inch(4,8, A, -))--convert Arabic numerals to Chinese uppercase form Set @money_chn=@money_chn+substring('0 One and three Woolu Qi Ba Jiu',@n_chn+1,1) if @n_chn<>'0' or @i inch(4,8, A)--Add a Chinese unit Set @money_chn=@money_chn+substring('Thousands of thousand hundred million thousand to pick up the round corner points',@i,1) Set @i=@i+1 End Set @money_chn=Replace(@money_chn,'Billions','billion')--when the amount is x million zero million if @money=0 Set @money_chn='0 Rounding' --returns ' 0 rounding ' when amount is zero if @n_chn='0' Set @money_chn=@money_chn+'Whole' --when the end of the amount is 0 ticks ending with ' whole ' return @money_chn --Return uppercase AmountEndGo
--select Dbo.fn_getformatmoney (888.88)
SQL amount to uppercase