sql 语句 编写一个函数实现将货币值用中文输出，例如12340.56，输出为:壹万二仟叁佰肆拾元伍角六分

 

create function exchange(@money float) returns varchar(100)

as

begin

declare @str1 varchar(30),@str2 varchar(100),@str varchar(2),@i int,@j int

set @str1=Rtrim(Ltrim(str(@money,20,2)))

set @str2=replicate(' ',100)

set @i=1

set @j=@i

while(@i<=len(@str1))

begin

if substring(@str1,@i,1)!='.'

begin

set @str=case substring(@str1,@i,1)

when '0' then '零'

when '1' then '壹'

when '2' then '贰'

when '3' then '叁'

when '4' then '肆'

when '5' then '伍'

when '6' then '陆'

when '7' then '柒'

when '8' then '捌'

when '9' then '玖'

end

set @str2=stuff(@str2,2*@j-1,1,@str)

set @str=case len(@str1)-@i+1

when 1 then '分'

when 2 then '角'

when 4 then '元'

when 5 then '拾'

when 6 then '佰'

when 7 then '仟'

when 8 then '万'

when 9 then '拾'

when 10 then '佰'

when 11 then '仟'

when 12 then '亿'

when 13 then '拾'

when 14 then '佰'

when 15 then '仟'

when 16 then '万'

end

set @str2=stuff(@str2,2*@j,1,@str)

end

else

set @j=@j-1

set @i=@i+1

set @j=@j+1

end

set @str2=Rtrim(@str2)

return @str2

end

 

select dbo.exchange(12348.56)