数字金额转大写SQL函数实现
1
set ANSI_NULLS ON
2set QUOTED_IDENTIFIER ON
3go
4
5ALTER FUNCTION [dbo].[FN_CONTRACT_MONEY_UPPER] (@n_LowerMoney numeric(15,2),@v_TransType int)
6RETURNS VARCHAR(200) AS
7BEGIN
8Declare @v_LowerStr VARCHAR(200) -- 小写金额
9Declare @v_UpperPart VARCHAR(200)
10Declare @v_UpperStr VARCHAR(200) -- 大写金额
11Declare @i_I int
12
13set @v_LowerStr = LTRIM(RTRIM(ROUND(@n_LowerMoney,-2))) --四舍五入为指定的精度并删除数据左右空格--精确到百位
14set @i_I = 1
15set @v_UpperStr = ''
16
17while ( @i_I <= len(@v_LowerStr))
18begin
19 select @v_UpperPart = case substring(@v_LowerStr,len(@v_LowerStr) - @i_I + 1,1)
20 WHEN '.' THEN '元'
21 WHEN '0' THEN '零'
22 WHEN '1' THEN '壹'
23 WHEN '2' THEN '贰'
24 WHEN '3' THEN '叁'
25 WHEN '4' THEN '肆'
26 WHEN '5' THEN '伍'
27 WHEN '6' THEN '陆'
28 WHEN '7' THEN '柒'
29 WHEN '8' THEN '捌'
30 WHEN '9' THEN '玖'
31 END
32 +
33 case @i_I
34 WHEN 1 THEN '分'
35 WHEN 2 THEN '角'
36 WHEN 3 THEN ''
37 WHEN 4 THEN ''
38 WHEN 5 THEN '拾'
39 WHEN 6 THEN '佰'
40 WHEN 7 THEN '仟'
41 WHEN 8 THEN '万'
42 WHEN 9 THEN '拾'
43 WHEN 10 THEN '佰'
44 WHEN 11 THEN '仟'
45 WHEN 12 THEN '亿'
46 WHEN 13 THEN '拾'
47 WHEN 14 THEN '佰'
48 WHEN 15 THEN '仟'
49 WHEN 16 THEN '万'
50 ELSE ''
51 END
52set @v_UpperStr = @v_UpperPart + @v_UpperStr
53set @i_I = @i_I + 1
54end
55
56if ( 0 = @v_TransType)
57begin
58set @v_UpperStr = REPLACE(@v_UpperStr,'零拾','零')
59set @v_UpperStr = REPLACE(@v_UpperStr,'零佰','零')
60set @v_UpperStr = REPLACE(@v_UpperStr,'零仟','零')
61set @v_UpperStr = REPLACE(@v_UpperStr,'零零零','零')
62set @v_UpperStr = REPLACE(@v_UpperStr,'零零','零')
63set @v_UpperStr = REPLACE(@v_UpperStr,'零角零分','整')
64set @v_UpperStr = REPLACE(@v_UpperStr,'零分','整')
65set @v_UpperStr = REPLACE(@v_UpperStr,'零角','零')
66set @v_UpperStr = REPLACE(@v_UpperStr,'零亿零万零元','亿元')
67set @v_UpperStr = REPLACE(@v_UpperStr,'亿零万零元','亿元')
68set @v_UpperStr = REPLACE(@v_UpperStr,'零亿零万','亿')
69set @v_UpperStr = REPLACE(@v_UpperStr,'零万零元','万元')
70set @v_UpperStr = REPLACE(@v_UpperStr,'万零元','万元')
71set @v_UpperStr = REPLACE(@v_UpperStr,'零亿','亿')
72set @v_UpperStr = REPLACE(@v_UpperStr,'零万','万')
73set @v_UpperStr = REPLACE(@v_UpperStr,'零元','元')
74set @v_UpperStr = REPLACE(@v_UpperStr,'零零','零')
75end
76
77-- 对壹元以下的金额的处理
78if ( '元' = substring(@v_UpperStr,1,1))
79begin
80 set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
81end
82
83if ( '零' = substring(@v_UpperStr,1,1))
84begin
85 set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
86end
87
88if ( '角' = substring(@v_UpperStr,1,1))
89begin
90 set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
91end
92
93if ( '分' = substring(@v_UpperStr,1,1))
94begin
95 set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
96end
97
98if ('整' = substring(@v_UpperStr,1,1))
99begin
100 set @v_UpperStr = '零元整'
101end
102
103return replace(replace(replace(replace(replace(replace(@v_UpperStr,'零角',''),'零分',''),'零元',''),'零拾',''),'零佰',''),'零仟','')+'圆整'
104END
set ANSI_NULLS ON2
set QUOTED_IDENTIFIER ON3
go4
5
ALTER FUNCTION [dbo].[FN_CONTRACT_MONEY_UPPER] (@n_LowerMoney numeric(15,2),@v_TransType int) 6
RETURNS VARCHAR(200) AS 7
BEGIN 8
Declare @v_LowerStr VARCHAR(200) -- 小写金额 9
Declare @v_UpperPart VARCHAR(200) 10
Declare @v_UpperStr VARCHAR(200) -- 大写金额11
Declare @i_I int12
13
set @v_LowerStr = LTRIM(RTRIM(ROUND(@n_LowerMoney,-2))) --四舍五入为指定的精度并删除数据左右空格--精确到百位14
set @i_I = 115
set @v_UpperStr = ''16
17
while ( @i_I <= len(@v_LowerStr))18
begin19
select @v_UpperPart = case substring(@v_LowerStr,len(@v_LowerStr) - @i_I + 1,1)20
WHEN '.' THEN '元'21
WHEN '0' THEN '零'22
WHEN '1' THEN '壹'23
WHEN '2' THEN '贰'24
WHEN '3' THEN '叁'25
WHEN '4' THEN '肆'26
WHEN '5' THEN '伍'27
WHEN '6' THEN '陆'28
WHEN '7' THEN '柒'29
WHEN '8' THEN '捌'30
WHEN '9' THEN '玖'31
END32
+ 33
case @i_I34
WHEN 1 THEN '分'35
WHEN 2 THEN '角'36
WHEN 3 THEN ''37
WHEN 4 THEN ''38
WHEN 5 THEN '拾'39
WHEN 6 THEN '佰'40
WHEN 7 THEN '仟'41
WHEN 8 THEN '万'42
WHEN 9 THEN '拾'43
WHEN 10 THEN '佰'44
WHEN 11 THEN '仟'45
WHEN 12 THEN '亿'46
WHEN 13 THEN '拾'47
WHEN 14 THEN '佰'48
WHEN 15 THEN '仟'49
WHEN 16 THEN '万'50
ELSE ''51
END52
set @v_UpperStr = @v_UpperPart + @v_UpperStr53
set @i_I = @i_I + 154
end55
56
if ( 0 = @v_TransType)57
begin58
set @v_UpperStr = REPLACE(@v_UpperStr,'零拾','零') 59
set @v_UpperStr = REPLACE(@v_UpperStr,'零佰','零') 60
set @v_UpperStr = REPLACE(@v_UpperStr,'零仟','零') 61
set @v_UpperStr = REPLACE(@v_UpperStr,'零零零','零')62
set @v_UpperStr = REPLACE(@v_UpperStr,'零零','零')63
set @v_UpperStr = REPLACE(@v_UpperStr,'零角零分','整')64
set @v_UpperStr = REPLACE(@v_UpperStr,'零分','整')65
set @v_UpperStr = REPLACE(@v_UpperStr,'零角','零')66
set @v_UpperStr = REPLACE(@v_UpperStr,'零亿零万零元','亿元')67
set @v_UpperStr = REPLACE(@v_UpperStr,'亿零万零元','亿元')68
set @v_UpperStr = REPLACE(@v_UpperStr,'零亿零万','亿')69
set @v_UpperStr = REPLACE(@v_UpperStr,'零万零元','万元')70
set @v_UpperStr = REPLACE(@v_UpperStr,'万零元','万元')71
set @v_UpperStr = REPLACE(@v_UpperStr,'零亿','亿')72
set @v_UpperStr = REPLACE(@v_UpperStr,'零万','万')73
set @v_UpperStr = REPLACE(@v_UpperStr,'零元','元')74
set @v_UpperStr = REPLACE(@v_UpperStr,'零零','零')75
end76
77
-- 对壹元以下的金额的处理 78
if ( '元' = substring(@v_UpperStr,1,1))79
begin80
set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))81
end82
83
if ( '零' = substring(@v_UpperStr,1,1))84
begin85
set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))86
end87
88
if ( '角' = substring(@v_UpperStr,1,1))89
begin90
set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))91
end92
93
if ( '分' = substring(@v_UpperStr,1,1))94
begin95
set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))96
end97
98
if ('整' = substring(@v_UpperStr,1,1))99
begin100
set @v_UpperStr = '零元整'101
end102
103
return replace(replace(replace(replace(replace(replace(@v_UpperStr,'零角',''),'零分',''),'零元',''),'零拾',''),'零佰',''),'零仟','')+'圆整'104
END