SQL Server中文大写金额转化函数

CREATE FUNCTION usf_ChineseCost

02

(

03	@Cost float

04

)

05	RETURNS varchar(50)

06

AS

07	----大写钱数----

08

BEGIN

09	declare @returnStr varchar(50)

10	if (@Cost >= 1000000000)

11

Begin

12	set @returnStr = '##########'

13	return @returnStr

14

end

15	set @returnStr = '00000000000'

16	set @returnStr = right(@returnStr + convert(varchar,convert(decimal(11,2),round(@cost,2))),12)

17	set @returnStr = replace(@returnStr,'0','零')

18	set @returnStr = replace(@returnStr,'1','壹')

19	set @returnStr = replace(@returnStr,'2','贰')

20	set @returnStr = replace(@returnStr,'3','叁')

21	set @returnStr = replace(@returnStr,'4','肆')

22	set @returnStr = replace(@returnStr,'5','伍')

23	set @returnStr = replace(@returnStr,'6','陆')

24	set @returnStr = replace(@returnStr,'7','柒')

25	set @returnStr = replace(@returnStr,'8','捌')

26	set @returnStr = replace(@returnStr,'9','玖')

27

28	set @returnStr = @returnStr + '分'

29	set @returnStr =  stuff(@returnStr,len(@returnStr)-1,0,'角')

30	set @returnStr =  replace(@returnStr,'.','元')

31	set @returnStr =  stuff(@returnStr,len(@returnStr)-5,0,'拾')

32	set @returnStr =  stuff(@returnStr,len(@returnStr)-7,0,'百')

33	set @returnStr =  stuff(@returnStr,len(@returnStr)-9,0,'千')

34	set @returnStr =  stuff(@returnStr,len(@returnStr)-11,0,'万')

35	set @returnStr =  stuff(@returnStr,len(@returnStr)-13,0,'拾')

36	set @returnStr =  stuff(@returnStr,len(@returnStr)-15,0,'百')

37	set @returnStr =  stuff(@returnStr,len(@returnStr)-17,0,'千')

38	set @returnStr =  stuff(@returnStr,len(@returnStr)-19,0,'亿')

39

40	set @returnStr =  replace(@returnStr,'零亿','零')

41	set @returnStr =  replace(@returnStr,'零千','零')

42	set @returnStr =  replace(@returnStr,'零百','零')

43	set @returnStr =  replace(@returnStr,'零拾','零')

44	set @returnStr =  replace(@returnStr,'零零零','零')

45	set @returnStr =  replace(@returnStr,'零零','零')

46	set @returnStr =  replace(@returnStr,'零万','万')

47	set @returnStr =  replace(@returnStr,'零元','元')

48	set @returnStr =  replace(@returnStr,'零角','零')

49	set @returnStr =  replace(@returnStr,'零分','')

50

51	while left(@returnStr,1) = '零'

52

Begin

53	set @returnStr =  right(@returnStr,len(@returnStr)-1)

54

end

55	if ((left(@returnStr,1)='万'))

56

begin

57	set @returnStr =  right(@returnStr,len(@returnStr)-1)

58

end

59	if ((left(@returnStr,1)='元'))

60

begin

61	set @returnStr =  right(@returnStr,len(@returnStr)-1)

62

end

63	while left(@returnStr,1) = '零'

64

Begin

65	set @returnStr =  right(@returnStr,len(@returnStr)-1)

66

end

67	while right(@returnStr,1) = '零'

68

Begin

69	set @returnStr =  left(@returnStr,len(@returnStr)-1)

70

end

71

72	set @returnStr =  replace(@returnStr,'亿万','亿零')

73	set @returnStr =  replace(@returnStr,'零元','元')

74	set @returnStr =  replace(@returnStr,'零零零','零')

75	set @returnStr =  replace(@returnStr,'零零','零')

76

77	if (@returnStr='')

78

begin

79	set @returnStr =  '零元'

80

end

81	if ((right(@returnStr,1)='元'))

82

begin

83	set @returnStr =  @returnStr + '整'

84

end

85	return @returnStr

86

END

转自：http://www.oschina.net/code/snippet_102716_1012