数字金额转大写SQL函数实现
1![]()
set ANSI_NULLS ON
2![]()
set QUOTED_IDENTIFIER ON
3![]()
go
4![]()
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 int
12![]()
13![]()
set @v_LowerStr = LTRIM(RTRIM(ROUND(@n_LowerMoney,-2))) --四舍五入为指定的精度并删除数据左右空格--精确到百位
14![]()
set @i_I = 1
15![]()
set @v_UpperStr = ''
16![]()
17![]()
while ( @i_I <= len(@v_LowerStr))
18![]()
begin
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
52![]()
set @v_UpperStr = @v_UpperPart + @v_UpperStr
53![]()
set @i_I = @i_I + 1
54![]()
end
55![]()
56![]()
if ( 0 = @v_TransType)
57![]()
begin
58![]()
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![]()
end
76![]()
77![]()
-- 对壹元以下的金额的处理
78![]()
if ( '元' = substring(@v_UpperStr,1,1))
79![]()
begin
80![]()
set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
81![]()
end
82![]()
83![]()
if ( '零' = substring(@v_UpperStr,1,1))
84![]()
begin
85![]()
set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
86![]()
end
87![]()
88![]()
if ( '角' = substring(@v_UpperStr,1,1))
89![]()
begin
90![]()
set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
91![]()
end
92![]()
93![]()
if ( '分' = substring(@v_UpperStr,1,1))
94![]()
begin
95![]()
set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
96![]()
end
97![]()
98![]()
if ('整' = substring(@v_UpperStr,1,1))
99![]()
begin
100![]()
set @v_UpperStr = '零元整'
101![]()
end
102![]()
103![]()
return replace(replace(replace(replace(replace(replace(@v_UpperStr,'零角',''),'零分',''),'零元',''),'零拾',''),'零佰',''),'零仟','')+'圆整'
104![]()
END
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
浙公网安备 33010602011771号