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

 

01CREATE FUNCTION usf_ChineseCost

02(

03@Cost float

04)

05RETURNS varchar(50)

06AS

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

08BEGIN

09declare@returnStrvarchar(50)

10if (@Cost >= 1000000000)

11    Begin

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

13        return@returnStr

14    end

15set@returnStr ='00000000000'

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

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

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

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

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

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

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

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

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

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

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

27         

28set@returnStr = @returnStr +'分'

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

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

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

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

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

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

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

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

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

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

39 

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

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

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

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

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

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

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

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

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

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

50 

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

52    Begin

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

54    end

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

56begin

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

58end

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

60begin

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

62end

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

64    Begin

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

66    end

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

68    Begin

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

70    end

71 

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

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

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

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

76 

77if (@returnStr='')

78begin

79  set@returnStr = '零元'

80end

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

82begin

83  set@returnStr =  @returnStr +'整'

84end

85return@returnStr

86END