/*
*******************************************************
作者:(birdie_7761@cmmail.com)
版本:1.0
创建时间:20020227
修改时间:
功能:小写金额转换成大写
参数:n_LowerMoney 小写金额
v_TransType 种类 -- 1: directly translate, 0: read it in words
输出:大写金额
*******************************************************
*/
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
'
[dbo].[L2U]
'
)
and
xtype
in
(N
'
FN
'
, N
'
IF
'
, N
'
TF
'
))
drop
function
[
dbo
]
.
[
L2U
]
GO
CREATE
FUNCTION
dbo.L2U(
@n_LowerMoney
numeric(
15
,
2
),
@v_TransType
int
)
RETURNS
VARCHAR
(
200
)
AS
BEGIN
Declare
@v_LowerStr
VARCHAR
(
200
)
--
小写金额
Declare
@v_UpperPart
VARCHAR
(
200
)
Declare
@v_UpperStr
VARCHAR
(
200
)
--
大写金额
Declare
@i_I
int
set
@v_LowerStr
=
LTRIM
(
RTRIM
(
ROUND
(
@n_LowerMoney
,
2
)))
--
四舍五入为指定的精度并删除数据左右空格
set
@i_I
=
1
set
@v_UpperStr
=
''
while
(
@i_I
<=
len
(
@v_LowerStr
))
begin
select
@v_UpperPart
=
case
substring
(
@v_LowerStr
,
len
(
@v_LowerStr
)
-
@i_I
+
1
,
1
)
WHEN
'
.
'
THEN
'
元
'
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
+
case
@i_I
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
'
拾
'
WHEN
10
THEN
'
佰
'
WHEN
11
THEN
'
仟
'
WHEN
12
THEN
'
亿
'
WHEN
13
THEN
'
拾
'
WHEN
14
THEN
'
佰
'
WHEN
15
THEN
'
仟
'
WHEN
16
THEN
'
万
'
ELSE
''
END
set
@v_UpperStr
=
@v_UpperPart
+
@v_UpperStr
set
@i_I
=
@i_I
+
1
end
if
(
0
=
@v_TransType
)
begin
set
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零拾
'
,
'
零
'
)
set
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零佰
'
,
'
零
'
)
set
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零仟
'
,
'
零
'
)
set
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零零零
'
,
'
零
'
)
set
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零零
'
,
'
零
'
)
set
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零角零分
'
,
'
整
'
)
set
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零分
'
,
'
整
'
)
set
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零角
'
,
'
零
'
)
set
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零亿零万零元
'
,
'
亿元
'
)
set
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
亿零万零元
'
,
'
亿元
'
)
set
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零亿零万
'
,
'
亿
'
)
set
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零万零元
'
,
'
万元
'
)
set
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
万零元
'
,
'
万元
'
)
set
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零亿
'
,
'
亿
'
)
set
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零万
'
,
'
万
'
)
set
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零元
'
,
'
元
'
)
set
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零零
'
,
'
零
'
)
end
--
对壹元以下的金额的处理
if
(
'
元
'
=
substring
(
@v_UpperStr
,
1
,
1
))
begin
set
@v_UpperStr
=
substring
(
@v_UpperStr
,
2
,(
len
(
@v_UpperStr
)
-
1
))
end
if
(
'
零
'
=
substring
(
@v_UpperStr
,
1
,
1
))
begin
set
@v_UpperStr
=
substring
(
@v_UpperStr
,
2
,(
len
(
@v_UpperStr
)
-
1
))
end
if
(
'
角
'
=
substring
(
@v_UpperStr
,
1
,
1
))
begin
set
@v_UpperStr
=
substring
(
@v_UpperStr
,
2
,(
len
(
@v_UpperStr
)
-
1
))
end
if
(
'
分
'
=
substring
(
@v_UpperStr
,
1
,
1
))
begin
set
@v_UpperStr
=
substring
(
@v_UpperStr
,
2
,(
len
(
@v_UpperStr
)
-
1
))
end
if
(
'
整
'
=
substring
(
@v_UpperStr
,
1
,
1
))
begin
set
@v_UpperStr
=
'
零元整
'
end
return
@v_UpperStr
END
GO
select
dbo.L2U(
12.93
,
1
),dbo.L2U(
12.93
,
0
)
CREATE
PROCEDURE
dbo.L2U
(
@n_LowerMoney
numeric(
15
,
2
),
@v_TransType
int
,
@RET
VARCHAR
(
200
) output
)
AS
Declare
@v_LowerStr
VARCHAR
(
200
)
--
小写金额
Declare
@v_UpperPart
VARCHAR
(
200
)
Declare
@v_UpperStr
VARCHAR
(
200
)
--
大写金额
Declare
@i_I
int
set
nocount
On
select
@v_LowerStr
=
LTRIM
(
RTRIM
(
STR
(
@n_LowerMoney
,
20
,
2
)))
--
四舍五入为指定的精度并删除数据左右空格
select
@i_I
=
1
select
@v_UpperStr
=
''''
while
(
@i_I
<=
len
(
@v_LowerStr
))
begin
select
@v_UpperPart
=
case
substring
(
@v_LowerStr
,
len
(
@v_LowerStr
)
-
@i_I
+
1
,
1
)
WHEN
'
.
'
THEN
'
元
'
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
+
case
@i_I
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
'
拾
'
WHEN
10
THEN
'
佰
'
WHEN
11
THEN
'
仟
'
WHEN
12
THEN
'
亿
'
WHEN
13
THEN
'
拾
'
WHEN
14
THEN
'
佰
'
WHEN
15
THEN
'
仟
'
WHEN
16
THEN
'
万
'
ELSE
''''
END
select
@v_UpperStr
=
@v_UpperPart
+
@v_UpperStr
select
@i_I
=
@i_I
+
1
end
--
------print '//v_UpperStr ='+@v_UpperStr +'//'
if
(
@v_TransType
=
0
)
begin
select
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零拾
'
,
'
零
'
)
select
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零佰
'
,
'
零
'
)
select
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零仟
'
,
'
零
'
)
select
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零零零
'
,
'
零
'
)
select
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零零
'
,
'
零
'
)
select
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零角零分
'
,
'
整
'
)
select
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零分
'
,
'
整
'
)
select
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零角
'
,
'
零
'
)
select
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零亿零万零元
'
,
'
亿元
'
)
select
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
亿零万零元
'
,
'
亿元
'
)
select
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零亿零万
'
,
'
亿
'
)
select
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零万零元
'
,
'
万元
'
)
select
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
万零元
'
,
'
万元
'
)
select
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零亿
'
,
'
亿
'
)
select
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零万
'
,
'
万
'
)
select
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零元
'
,
'
元
'
)
select
@v_UpperStr
=
REPLACE
(
@v_UpperStr
,
'
零零
'
,
'
零
'
)
end
--
对壹元以下的金额的处理
if
(
substring
(
@v_UpperStr
,
1
,
1
)
=
'
元
'
)
begin
select
@v_UpperStr
=
substring
(
@v_UpperStr
,
2
,(
len
(
@v_UpperStr
)
-
1
))
end
if
(
substring
(
@v_UpperStr
,
1
,
1
)
=
'
零
'
)
begin
select
@v_UpperStr
=
substring
(
@v_UpperStr
,
2
,(
len
(
@v_UpperStr
)
-
1
))
end
if
(
substring
(
@v_UpperStr
,
1
,
1
)
=
'
角
'
)
begin
select
@v_UpperStr
=
substring
(
@v_UpperStr
,
2
,(
len
(
@v_UpperStr
)
-
1
))
end
if
(
substring
(
@v_UpperStr
,
1
,
1
)
=
'
分
'
)
begin
select
@v_UpperStr
=
substring
(
@v_UpperStr
,
2
,(
len
(
@v_UpperStr
)
-
1
))
end
if
(
substring
(
@v_UpperStr
,
1
,
1
)
=
'
整
'
)
begin
select
@v_UpperStr
=
'
零元整
'
end
select
@ret
=
@v_UpperStr
GO
--
调用过程:
declare
@ret
varchar
(
200
)
exec
L2U
567983.897
,
1
,
@ret
output
select
@ret
CREATE
FUNCTION
[
dbo
]
.
[
f_num_chn
]
(
@num
numeric(
14
,
2
))
RETURNS
varchar
(
100
)
WITH
ENCRYPTION
AS
BEGIN
--
版权所有:pbsql
DECLARE
@n_data
VARCHAR
(
20
),
@c_data
VARCHAR
(
100
),
@n_str
VARCHAR
(
10
),
@i
int
SET
@n_data
=RIGHT
(
SPACE
(
14
)
+
CAST
(
CAST
(
ABS
(
@num
*
100
)
AS
bigint
)
AS
varchar
(
20
)),
14
)
SET
@c_data
=
''
SET
@i
=
1
WHILE
@i
<=
14
BEGIN
SET
@n_str
=
SUBSTRING
(
@n_data
,
@i
,
1
)
IF
@n_str
<>
'
'
BEGIN
IF
not
((
SUBSTRING
(
@n_data
,
@i
,
2
)
=
'
00
'
)
or
((
@n_str
=
'
0
'
)
and
((
@i
=
4
)
or
(
@i
=
8
)
or
(
@i
=
12
)
or
(
@i
=
14
))))
SET
@c_data
=
@c_data
+
SUBSTRING
(
'
零壹贰叁肆伍陆柒捌玖
'
,
CAST
(
@n_str
AS
int
)
+
1
,
1
)
IF
not
((
@n_str
=
'
0
'
)
and
(
@i
<>
4
)
and
(
@i
<>
8
)
and
(
@i
<>
12
))
SET
@c_data
=
@c_data
+
SUBSTRING
(
'
仟佰拾亿仟佰拾万仟佰拾圆角分
'
,
@i
,
1
)
IF
SUBSTRING
(
@c_data
,
LEN
(
@c_data
)
-
1
,
2
)
=
'
亿万
'
SET
@c_data
=
SUBSTRING
(
@c_data
,
1
,
LEN
(
@c_data
)
-
1
)
END
SET
@i
=
@i
+
1
END
IF
@num
<
0
SET
@c_data
=
'
(负数)
'
+
@c_data
IF
@num
=
0
SET
@c_data
=
'
零圆
'
IF
@n_str
=
'
0
'
SET
@c_data
=
@c_data
+
'
整
'
RETURN
(
@c_data
)
END
GO
CREATE FUNCTION [dbo].[f_num_chn] (@num numeric(14,2))
RETURNS varchar(100) WITH ENCRYPTION
AS
BEGIN
--版权所有:pbsql
DECLARE @n_data VARCHAR(20),@c_data VARCHAR(100),@n_str VARCHAR(10),@i int
SET @n_data=RIGHT(SPACE(14)+CAST(CAST(ABS(@num*100) AS bigint) AS varchar(20)),14)
SET @c_data=''
SET @i=1
WHILE @i<=14
BEGIN
SET @n_str=SUBSTRING(@n_data,@i,1)
IF @n_str<>' '
BEGIN
IF not ((SUBSTRING(@n_data,@i,2)='00') or
((@n_str='0') and ((@i=4) or (@i=8) or (@i=12) or (@i=14)))) SET @c_data=@c_data+SUBSTRING('零壹贰叁肆伍陆柒捌玖',CAST(@n_str AS int)+1,1)
IF not ((@n_str='0') and (@i<>4) and (@i<>8) and (@i<>12))
SET @c_data=@c_data+SUBSTRING('仟佰拾亿仟佰拾万仟佰拾圆角分',@i,1)
IF SUBSTRING(@c_data,LEN(@c_data)-1,2)='亿万'
SET @c_data=SUBSTRING(@c_data,1,LEN(@c_data)-1)
END
SET @i=@i+1
END
IF @num<0
SET @c_data='(负数)'+@c_data
IF @num=0
SET @c_data='零圆'
IF @n_str='0'
SET @c_data=@c_data+'整'
RETURN(@c_data)
END
转载于:https://www.cnblogs.com/nosnowwolf/articles/1044466.html