又发现一个BUG,if @J<=2 and @lastJ<=3 改成:if @J<=2 and @lastJ<3CREATE FUNCTION GetChineseNum (@inputId money)
RETURNS Nvarchar(4000) AS
BEGIN
declare @rV Nvarchar(4000)declare @tmpstr Nvarchar(4000),@M Nvarchar(4000),@K Nvarchar(4000),@I numeric(38,2),
@J int,@lastJ int,@LastV Nvarchar(10),@LastF Nvarchar(10),@LastE Nvarchar(10),@LastVE Nvarchar(10)
set @I=@inputId
select @tmpstr=N'零壹贰叁肆伍陆柒捌玖分角元拾佰仟万拾佰仟亿拾佰仟',@K=N'',@M=cast(cast(@I*100 as bigint) as varchar(800)),@J=len(@M),@LastVE=N''while @J>=1
begin
set @LastF=substring(@tmpstr, cast(substring(@m,len(@M)-@j+1,1) as bigint)+1,1)
set @LastE=substring(@tmpstr,10+@J,1)
if @LastF<>N'零'
begin
if @LastV=N'零'
if (@lastJ>=7 and @j<=7) or (@lastJ>=11 and @j<=11 ) or (@lastJ>=3 and @j<=2)
if @J<=2 and @lastJ<3
set @K=@K+@LastVE+@LastF+@LastE
else
set @K=@K+@LastVE+@LastV+@LastF+@LastE
else
set @K=@K+@LastV+@LastF+@LastE
else
set @K=@K+@LastF+@LastE
select @lastJ=@j,@LastVE=N''
end
else
begin
if @LastVE=N'' and @lastJ>11 set @LastVE=N'亿'
if @LastVE=N'' and @lastJ>7 and @lastJ<10 set @LastVE=N'万'
if @LastVE=N'' and @lastJ>3 and @lastJ<6 set @LastVE=N'元'
if @LastV<>N'零' set @lastJ=@j
end
set @LastV=@LastF
set @J=@J-1
end
if @lastJ>=3 set @K=@K+N'元'
if @lastJ>=2 set @K=@K+N'整'set @rv=@K return @rv
END
RETURNS Nvarchar(4000) AS
BEGIN
declare @rV Nvarchar(4000)declare @tmpstr Nvarchar(4000),@M Nvarchar(4000),@K Nvarchar(4000),@I numeric(38,2),
@J int,@lastJ int,@LastV Nvarchar(10),@LastF Nvarchar(10),@LastE Nvarchar(10),@LastVE Nvarchar(10)
set @I=@inputId
select @tmpstr=N'零壹贰叁肆伍陆柒捌玖分角元拾佰仟万拾佰仟亿拾佰仟',@K=N'',@M=cast(cast(@I*100 as bigint) as varchar(800)),@J=len(@M),@LastVE=N''while @J>=1
begin
set @LastF=substring(@tmpstr, cast(substring(@m,len(@M)-@j+1,1) as bigint)+1,1)
set @LastE=substring(@tmpstr,10+@J,1)
if @LastF<>N'零'
begin
if @LastV=N'零'
if (@lastJ>=7 and @j<=7) or (@lastJ>=11 and @j<=11 ) or (@lastJ>=3 and @j<=2)
if @J<=2 and @lastJ<3
set @K=@K+@LastVE+@LastF+@LastE
else
set @K=@K+@LastVE+@LastV+@LastF+@LastE
else
set @K=@K+@LastV+@LastF+@LastE
else
set @K=@K+@LastF+@LastE
select @lastJ=@j,@LastVE=N''
end
else
begin
if @LastVE=N'' and @lastJ>11 set @LastVE=N'亿'
if @LastVE=N'' and @lastJ>7 and @lastJ<10 set @LastVE=N'万'
if @LastVE=N'' and @lastJ>3 and @lastJ<6 set @LastVE=N'元'
if @LastV<>N'零' set @lastJ=@j
end
set @LastV=@LastF
set @J=@J-1
end
if @lastJ>=3 set @K=@K+N'元'
if @lastJ>=2 set @K=@K+N'整'set @rv=@K return @rv
END
你好,真的很感谢。我想问一下,有没有这方面的资料,给我一点看看[email protected]
select dbo.getchinesenum(1020004.3+1) --壹佰万零贰万零伍元叁角
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 intset nocount onselect @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))
endif (substring(@v_UpperStr,1,1)= '零')
begin
select @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
endif (substring(@v_UpperStr,1,1)='角')
begin
select @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
endif ( substring(@v_UpperStr,1,1)='分')
begin
select @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
endif (substring(@v_UpperStr,1,1)='整')
begin
select @v_UpperStr = '零元整'
endselect @ret=@v_UpperStrGO调用过程:declare @ret varchar(200)exec L2U 567983.897,1,@ret outputselect @ret
改成:if (@lastJ>=7 and @j<7) or (@lastJ>=11 and @j<11 ) or(@lastJ>=3 and @j<=2)
再改改吧!
刚才0结尾没有追加单位,现在加上if @lastJ>=6 set @K=@K+@LastVE以上问题解决了。CREATE FUNCTION GetChineseNum (@inputId money)
RETURNS Nvarchar(4000) AS
BEGIN
declare @rV Nvarchar(4000)declare @tmpstr Nvarchar(4000),@M Nvarchar(4000),@K Nvarchar(4000),@I numeric(38,2),
@J int,@lastJ int,@LastV Nvarchar(10),@LastF Nvarchar(10),@LastE Nvarchar(10),@LastVE Nvarchar(10)
set @I=@inputId
select @tmpstr=N'零壹贰叁肆伍陆柒捌玖分角元拾佰仟万拾佰仟亿拾佰仟',@K=N'',@M=cast(cast(@I*100 as bigint) as varchar(800)),@J=len(@M),@LastVE=N''while @J>=1
begin
set @LastF=substring(@tmpstr, cast(substring(@m,len(@M)-@j+1,1) as bigint)+1,1)
set @LastE=substring(@tmpstr,10+@J,1)
if @LastF<>N'零'
begin
if @LastV=N'零'
if (@lastJ>=7 and @j<7) or (@lastJ>=11 and @j<11 ) or (@lastJ>=3 and @j<=2)
if @J<=2 and @lastJ<=3
set @K=@K+@LastVE+@LastF+@LastE
else
set @K=@K+@LastVE+@LastV+@LastF+@LastE
else
set @K=@K+@LastV+@LastF+@LastE
else
set @K=@K+@LastF+@LastE
select @lastJ=@j,@LastVE=N''
end
else
begin
if @LastVE=N'' and @lastJ>11 set @LastVE=N'亿'
if @LastVE=N'' and @lastJ>7 and @lastJ<10 set @LastVE=N'万'
if @LastVE=N'' and @lastJ>3 and @lastJ<6 set @LastVE=N'元'
if @LastV<>N'零' set @lastJ=@j
end
set @LastV=@LastF
set @J=@J-1
end
if @lastJ>=6 set @K=@K+@LastVE
if @lastJ>=3 set @K=@K+N'元'
if @lastJ>=2 set @K=@K+N'整'set @rv=@K return @rv
END
加长下面的字符串就可以处理了
@tmpstr=N'零壹贰叁肆伍陆柒捌玖分角元拾佰仟万拾佰仟亿拾佰仟'可惜最大的单位还是亿,我不知道再大要用什么单位了。
@J int
set @I=1005.00
set @tmpstr=N'零壹贰叁肆伍陆柒捌玖分角元拾佰仟万拾佰仟亿拾佰仟万拾佰仟万拾佰仟'
set @K=N''
set @M=cast(cast(@I*100 as bigint) as varchar(800))
set @J=len(@M)
while @J>=1
begin
set @k=@K+ substring(@tmpstr, cast(substring(@m,len(@M)-@j+1,1) as bigint)+1,1)
+substring(@tmpstr,10+@J,1)
set @j=@J-1
endprint @k
CREATE FUNCTION GetChineseNum (@inputId money)
RETURNS Nvarchar(4000) AS
BEGIN
declare @rV Nvarchar(4000)declare @tmpstr Nvarchar(4000),@M Nvarchar(4000),@K Nvarchar(4000),@I numeric(38,2),
@J int,@lastJ int,@LastV Nvarchar(10),@LastF Nvarchar(10),@LastE Nvarchar(10),@LastVE Nvarchar(10)
set @I=@inputId
set set @K=N''if @I<0
begin
set @I=0-@I
set @K=N'负'
end
if @I=0 return N'零元整'select @tmpstr=N'零壹贰叁肆伍陆柒捌玖分角元拾佰仟万拾佰仟亿拾佰仟',@M=cast(cast(@I*100 as bigint) as varchar(800)),@J=len(@M),@LastVE=N''
while @J>=1
begin
set @LastF=substring(@tmpstr, cast(substring(@m,len(@M)-@j+1,1) as bigint)+1,1)
set @LastE=substring(@tmpstr,10+@J,1)
if @LastF<>N'零'
begin
if @LastV=N'零'
if (@lastJ>=7 and @j<7) or (@lastJ>=11 and @j<11 ) or (@lastJ>=3 and @j<=2)
if @J<=2 and @lastJ<=3
set @K=@K+@LastVE+@LastF+@LastE
else
set @K=@K+@LastVE+@LastV+@LastF+@LastE
else
set @K=@K+@LastV+@LastF+@LastE
else
set @K=@K+@LastF+@LastE
select @lastJ=@j,@LastVE=N''
end
else
begin
if @LastVE=N'' and @lastJ>11 set @LastVE=N'亿'
if @LastVE=N'' and @lastJ>7 and @lastJ<10 set @LastVE=N'万'
if @LastVE=N'' and @lastJ>3 and @lastJ<6 set @LastVE=N'元'
if @LastV<>N'零' set @lastJ=@j
end
set @LastV=@LastF
set @J=@J-1
end
if @lastJ>=6 set @K=@K+@LastVE
if @lastJ>=3 set @K=@K+N'元'
if @lastJ>=2 set @K=@K+N'整'set @rv=@K return @rv
END
RETURNS Nvarchar(4000) AS
BEGIN
declare @rV Nvarchar(4000)declare @tmpstr Nvarchar(4000),@M Nvarchar(4000),@K Nvarchar(4000),@I numeric(38,2),
@J int,@lastJ int,@LastV Nvarchar(10),@LastF Nvarchar(10),@LastE Nvarchar(10),@LastVE Nvarchar(10)
set @I=@inputId
set set @K=N''if @I<0
begin
set @I=0-@I
set @K=N'负'
end
if @I=0 return N'零元整'select @tmpstr=N'零壹贰叁肆伍陆柒捌玖分角元拾佰仟万拾佰仟亿拾佰仟',@M=cast(cast(@I*100 as bigint) as varchar(800)),@J=len(@M),@LastVE=N''
while @J>=1
begin
set @LastF=substring(@tmpstr, cast(substring(@m,len(@M)-@j+1,1) as bigint)+1,1)
set @LastE=substring(@tmpstr,10+@J,1)
if @LastF<>N'零'
begin
if @LastV=N'零'
if (@lastJ>=7 and @j<7) or (@lastJ>=11 and @j<11 ) or (@lastJ>=3 and @j<=2)
if @J<=2 and @lastJ<=3
set @K=@K+@LastVE+@LastF+@LastE
else
set @K=@K+@LastVE+@LastV+@LastF+@LastE
else
set @K=@K+@LastV+@LastF+@LastE
else
set @K=@K+@LastF+@LastE
select @lastJ=@j,@LastVE=N''
end
else
begin
if @LastVE=N'' and @lastJ>11 set @LastVE=N'亿'
if @LastVE=N'' and @lastJ>7 and @lastJ<10 set @LastVE=N'万'
if @LastVE=N'' and @lastJ>3 and @lastJ<6 set @LastVE=N'元'
if @LastVE=N'' and @lastJ>2 set @LastVE=N'零'
if @LastV<>N'零' set @lastJ=@j
end
set @LastV=@LastF
set @J=@J-1
end
if @lastJ>=6 set @K=@K+@LastVE
if @lastJ>=3 set @K=@K+N'元'
if @lastJ>1 or (@lastJ=1 and @LastV=N'零') set @K=@K+N'整'set @rv=@K return @rv
END
这句为什么要用2个set呢??
RETURNS Nvarchar(4000) AS
BEGIN
declare @rV Nvarchar(4000)declare @tmpstr Nvarchar(4000),@M Nvarchar(4000),@K Nvarchar(4000),@I numeric(38,2),
@J int,@lastJ int,@LastV Nvarchar(10),@LastF Nvarchar(10),@LastE Nvarchar(10),@LastVE Nvarchar(10)
set @I=@inputId
set @K=N''if @I<0
begin
set @I=0-@I
set @K=N'负'
end
if @I=0 return N'零元整'select @tmpstr=N'零壹贰叁肆伍陆柒捌玖分角元拾佰仟万拾佰仟亿拾佰仟',@M=cast(cast(@I*100 as bigint) as varchar(800)),@J=len(@M),@LastVE=N''
while @J>=1
begin
set @LastF=substring(@tmpstr, cast(substring(@m,len(@M)-@j+1,1) as bigint)+1,1)
set @LastE=substring(@tmpstr,10+@J,1)
if @LastF<>N'零'
begin
if @LastV=N'零'
if (@lastJ>=7 and @j<7) or (@lastJ>=11 and @j<11 ) or (@lastJ>=3 and @j<=2)
if @J<=2 and @lastJ<=3
set @K=@K+@LastVE+@LastF+@LastE
else
set @K=@K+@LastVE+@LastV+@LastF+@LastE
else
set @K=@K+@LastV+@LastF+@LastE
else
set @K=@K+@LastF+@LastE
select @lastJ=@j,@LastVE=N''
end
else
begin
if @LastVE=N'' and @lastJ>11 set @LastVE=N'亿'
if @LastVE=N'' and @lastJ>7 and @lastJ<10 set @LastVE=N'万'
if @LastVE=N'' and @lastJ>3 and @lastJ<6 set @LastVE=N'元'
if @LastV<>N'零' set @lastJ=@j
if @j=2 and @lastJ=2 set @LastVE=N'零' end
set @LastV=@LastF
set @J=@J-1
end
if @lastJ>=6 or (@LastV=N'零' and @lastJ=1) set @K=@K+@LastVE
if @lastJ>=3 set @K=@K+N'元'
if @lastJ>=2 set @K=@K+N'整'--if @lastJ>1 or (@lastJ=1 and @LastV=N'零') set @K=@K+N'整'set @rv=@K return @rv
END
CREATE FUNCTION GetChineseNum (@inputId money)
RETURNS Nvarchar(4000) AS
BEGIN
declare @rV Nvarchar(4000)
declare @tmpstr Nvarchar(4000),@M Nvarchar(4000),@K Nvarchar(4000),@I numeric(38,2),
@J int,@lastJ int,@LastV Nvarchar(10),@LastF Nvarchar(10),@LastE Nvarchar(10),@LastVE Nvarchar(10)
set @I=@inputId
set @K=N''if @I<0
begin
set @I=0-@I
set @K=N'负'
end
if @I=0 return N'零元整'select @tmpstr=N'零壹贰叁肆伍陆柒捌玖分角元拾佰仟万拾佰仟亿拾佰仟',@M=cast(cast(@I*100 as bigint) as varchar(800)),@J=len(@M),@LastVE=N''
while @J>=1
begin
set @LastF=substring(@tmpstr, cast(substring(@m,len(@M)-@j+1,1) as bigint)+1,1)
set @LastE=substring(@tmpstr,10+@J,1)
if @LastF<>N'零'
begin
if @LastV=N'零'
if ((@lastJ>=7 and @j<7) or (@lastJ>=11 and @j<11 ) or (@lastJ>=3 and @j<3))
if (@J<=2 and @lastJ<=11 and @lastJ>2) or (@lastJ>=12 and @j=2 and @LastV=N'零')
set @K=@K+@LastVE+@LastF+@LastE
else
set @K=@K+@LastVE+@LastV+@LastF+@LastE
else
set @K=@K+@LastV+@LastF+@LastE
else
set @K=@K+@LastF+@LastE
select @lastJ=@j,@LastVE=N''
end
else
begin
if @LastVE=N'' and @lastJ>11 set @LastVE=N'亿'
if @LastVE=N'' and @lastJ>7 and @lastJ<10 set @LastVE=N'万'
if ((@LastVE=N'' and @lastJ<7) or (@LastVE=N'万'and @lastJ<11) or ((@LastVE=N'亿' or @LastVE=N'') and @lastJ>10)) set @LastVE=@LastVE+N'元' if @LastV<>N'零' set @lastJ=@j
if @j=2
if substring(@tmpstr, cast(substring(@m,len(@M)-@j+2,1) as bigint)+1,1)<>N'零'
and @lastJ<12 set @LastVE=@LastVE+N'零'
end
set @LastV=@LastF
set @J=@J-1
endif @lastJ>=6 or (@LastVE=N'零' and @lastJ=1) set @K=@K+@LastVE
if @lastJ>=3 and @lastJ<=5 set @K=@K+N'元'
if @lastJ>=2 set @K=@K+N'整'--if @lastJ>1 or (@lastJ=1 and @LastV=N'零') set @K=@K+N'整'set @rv=@K return @rv
END---------------------------------------------
1.0000 壹元整
10.0000 壹拾元整
100.0000 壹佰元整
1000.0000 壹仟元整
10000.0000 壹万元整
100000.0000 壹拾万元整
1000000.0000 壹佰万元整
10000000.0000 壹仟万元整
100000000.0000 壹亿元整
1000000000.0000 壹拾亿元整
10000000000.0000 壹佰亿元整
100000000000.0000 壹仟亿元整
1.0100 壹元零壹分
10.0100 壹拾元零壹分
100.0100 壹佰元零壹分
1000.0100 壹仟元零壹分
10000.0100 壹万元零壹分
100000.0100 壹拾万元零壹分
1000000.0100 壹佰万元零壹分
10000000.0100 壹仟万元零壹分
100000000.0100 壹亿元零壹分
1000000000.0100 壹拾亿元零壹分
10000000000.0100 壹佰亿元零壹分
100000000000.0100 壹仟亿元零壹分
1.5600 壹元伍角陆分
14.5600 壹拾肆元伍角陆分
145.5600 壹佰肆拾伍元伍角陆分
1365.5600 壹仟叁佰陆拾伍元伍角陆分
78946.3200 柒万捌仟玖佰肆拾陆元叁角贰分
789551.4500 柒拾捌万玖仟伍佰伍拾壹元肆角伍分
7986612.2300 柒佰玖拾捌万陆仟陆佰壹拾贰元贰角叁分
78854556.9600 柒仟捌佰捌拾伍万肆仟伍佰伍拾陆元玖角陆分
789511236.3600 柒亿捌仟玖佰伍拾壹万壹仟贰佰叁拾陆元叁角陆分
7892456221.9600 柒拾捌亿玖仟贰佰肆拾伍万陆仟贰佰贰拾壹元玖角陆分
48622425587.3600 肆佰捌拾陆亿贰仟贰佰肆拾贰万伍仟伍佰捌拾柒元叁角陆分
977459855512.8500 玖仟柒佰柒拾肆亿伍仟玖佰捌拾伍万伍仟伍佰壹拾贰元捌角伍分
1.1000 壹元壹角
10.1000 壹拾元壹角
1000.1000 壹仟元壹角
10000.1000 壹万元壹角
1000000.1000 壹佰万元壹角
100000000.1000 壹亿元壹角
10000000000.1000 壹佰亿元壹角
100000000000.1000 壹仟亿元壹角
CREATE FUNCTION GetChineseNum (@inputId money)
RETURNS Nvarchar(4000) AS
BEGIN
declare @rV Nvarchar(4000)declare @tmpstr Nvarchar(4000),@M Nvarchar(4000),@K Nvarchar(4000),@I numeric(38,2),
@J int,@lastJ int,@LastV Nvarchar(10),@LastF Nvarchar(10),@LastE Nvarchar(10),@LastVE Nvarchar(10)
set @I=@inputIdset @K=N''if @I<0
begin
set @I=0-@I
set @K=N'负'
end
if @I=0 return N'零元整'select @tmpstr=N'零壹贰叁肆伍陆柒捌玖分角元拾佰仟万拾佰仟亿拾佰仟',@M=cast(cast(@I*100 as bigint) as varchar(800)),@J=len(@M),@LastVE=N''
while @J>=1
begin
set @LastF=substring(@tmpstr, cast(substring(@m,len(@M)-@j+1,1) as bigint)+1,1)
set @LastE=substring(@tmpstr,10+@J,1)
if @LastF<>N'零'
begin if @LastV=N'零'
if ((@lastJ>=7 and @j<7) or (@lastJ>=11 and @j<11 ) or (@lastJ>=3 and @j<3))
if (@J<=2 and @lastJ<=11 and @lastJ>2) or (@lastJ>=12 and @j=2 and @LastV=N'零')
set @K=@K+@LastVE+@LastF+@LastE
else
set @K=@K+@LastVE+@LastV+@LastF+@LastE
else
set @K=@K+@LastV+@LastF+@LastE
else
set @K=@K+@LastF+@LastE
select @lastJ=@j,@LastVE=N''
end
else
begin
if @LastVE=N'' and @lastJ>11 set @LastVE=N'亿'
if @LastVE=N'' and @lastJ>7 and @lastJ<10 set @LastVE=N'万' if ((@LastVE=N'' and @lastJ<7) or (@LastVE=N'万'and @lastJ<11 and @lastJ>9) or ((@LastVE=N'亿' or @LastVE=N'') and @lastJ>15)) set @LastVE=@LastVE+N'元' if @LastV<>N'零' set @lastJ=@j
if @j=2
if substring(@tmpstr, cast(substring(@m,len(@M)-@j+2,1) as bigint)+1,1)<>N'零'
and @lastJ<12 set @LastVE=@LastVE+N'零'
end
set @LastV=@LastF
set @J=@J-1
endif @lastJ>=6 or (@LastVE=N'零' and @lastJ=1) set @K=@K+@LastVE
if @lastJ>=3 and @lastJ<=5 set @K=@K+N'元'
if @lastJ>=2 set @K=@K+N'整'set @rv=@K return @rv
END----------------------
10.0100 壹拾元零壹分
1010.0100 壹仟零壹拾元零壹分
100100.0100 壹拾万零壹佰元零壹分
10001000.0100 壹仟万零壹仟元零壹分
1000010000.0100 壹拾亿零壹万元零壹分
100000100000.0100 壹仟亿零壹拾万零壹分
RETURNS Nvarchar(4000) AS
BEGIN
declare @rV Nvarchar(4000)declare @tmpstr Nvarchar(4000),@M Nvarchar(4000),@K Nvarchar(4000),@I numeric(38,2),
@J int,@lastJ int,@LastV Nvarchar(10),@LastF Nvarchar(10),@LastE Nvarchar(10),@LastVE Nvarchar(10)
set @I=@inputIdset @K=N''if @I<0
begin
set @I=0-@I
set @K=N'负'
end
if @I=0 return N'零元整'select @tmpstr=N'零壹贰叁肆伍陆柒捌玖分角元拾佰仟万拾佰仟亿拾佰仟',@M=cast(cast(@I*100 as bigint) as varchar(800)),@J=len(@M),@LastVE=N''
while @J>=1
begin
set @LastF=substring(@tmpstr, cast(substring(@m,len(@M)-@j+1,1) as bigint)+1,1)
set @LastE=substring(@tmpstr,10+@J,1)
if @LastF<>N'零'
begin if @LastV=N'零'
if ((@lastJ>=7 and @j<7) or (@lastJ>=11 and @j<11 ) or (@lastJ>=3 and @j<3))
if (@J<=2 and @lastJ<=11 and @lastJ>2) or (@lastJ>=12 and @j=2 and @LastV=N'零')
set @K=@K+@LastVE+@LastF+@LastE
else
set @K=@K+@LastVE+@LastV+@LastF+@LastE
else
set @K=@K+@LastV+@LastF+@LastE
else
set @K=@K+@LastF+@LastE
select @lastJ=@j,@LastVE=N''
end
else
begin
if @LastVE=N'' and @lastJ>11 set @LastVE=N'亿'
if @LastVE=N'' and @lastJ>7 and @lastJ<10 set @LastVE=N'万' if ((@LastVE=N'' and @lastJ<7) or (@LastVE=N'万'and @lastJ<11 and @lastJ>9) or ((@LastVE=N'亿' or @LastVE=N'') and @lastJ>15 ))
or (@J=3 and @lastJ>=7)
set @LastVE=@LastVE+N'元' if @LastV<>N'零' set @lastJ=@j
if @j=2
if substring(@tmpstr, cast(substring(@m,len(@M)-@j+2,1) as bigint)+1,1)<>N'零'
and @lastJ<12 set @LastVE=@LastVE+N'零'
end
set @LastV=@LastF
set @J=@J-1
endif @lastJ>=6 or (@LastVE=N'零' and @lastJ=1) set @K=@K+@LastVE
if @lastJ>=3 and @lastJ<=5 set @K=@K+N'元'
if @lastJ>=2 or (@lastJ=1 and @lastV=N'零') set @K=@K+N'整'set @rv=@K return @rv
END------------------------------------------------------
1.0000 壹元整
10.0000 壹拾元整
100.0000 壹佰元整
1000.0000 壹仟元整
10000.0000 壹万元整
100000.0000 壹拾万元整
1000000.0000 壹佰万元整
10000000.0000 壹仟万元整
100000000.0000 壹亿元整
1000000000.0000 壹拾亿元整
10000000000.0000 壹佰亿元整
100000000000.0000 壹仟亿元整
1.0100 壹元零壹分
10.0100 壹拾元零壹分
100.0100 壹佰元零壹分
1000.0100 壹仟元零壹分
10000.0100 壹万元零壹分
100000.0100 壹拾万元零壹分
1000000.0100 壹佰万元零壹分
10000000.0100 壹仟万元零壹分
100000000.0100 壹亿元零壹分
1000000000.0100 壹拾亿元零壹分
10000000000.0100 壹佰亿元零壹分
100000000000.0100 壹仟亿元零壹分
1.5600 壹元伍角陆分
14.5600 壹拾肆元伍角陆分
145.5600 壹佰肆拾伍元伍角陆分
1365.5600 壹仟叁佰陆拾伍元伍角陆分
78946.3200 柒万捌仟玖佰肆拾陆元叁角贰分
789551.4500 柒拾捌万玖仟伍佰伍拾壹元肆角伍分
7986612.2300 柒佰玖拾捌万陆仟陆佰壹拾贰元贰角叁分
78854556.9600 柒仟捌佰捌拾伍万肆仟伍佰伍拾陆元玖角陆分
789511236.3600 柒亿捌仟玖佰伍拾壹万壹仟贰佰叁拾陆元叁角陆分
7892456221.9600 柒拾捌亿玖仟贰佰肆拾伍万陆仟贰佰贰拾壹元玖角陆分
48622425587.3600 肆佰捌拾陆亿贰仟贰佰肆拾贰万伍仟伍佰捌拾柒元叁角陆分
977459855512.8500 玖仟柒佰柒拾肆亿伍仟玖佰捌拾伍万伍仟伍佰壹拾贰元捌角伍分
1.1000 壹元壹角整
10.1000 壹拾元壹角整
1000.1000 壹仟元壹角整
10000.1000 壹万元壹角整
1000000.1000 壹佰万元壹角整
100000000.1000 壹亿元壹角整
10000000000.1000 壹佰亿元壹角整
100000000000.1000 壹仟亿元壹角整
1000010000.0100 壹拾亿零壹万元零壹分
10.0100 壹拾元零壹分
1010.0100 壹仟零壹拾元零壹分
100100.0100 壹拾万零壹佰元零壹分
10001000.0100 壹仟万零壹仟元零壹分
1000010000.0100 壹拾亿零壹万元零壹分
100000100001.0100 壹仟亿零壹拾万零壹元零壹分
1000.1500 壹仟元壹角伍分
1001000.1500 壹佰万零壹仟元壹角伍分
10001000.1500 壹仟万零壹仟元壹角伍分
100001000.1500 壹亿零壹仟元壹角伍分
1000001000.1500 壹拾亿零壹仟元壹角伍分
10000.0500 壹万元零伍分
110000.0500 壹拾壹万元零伍分
1010000.0500 壹佰零壹万元零伍分
10010000.0500 壹仟零壹万元零伍分
100010000.0500 壹亿零壹万元零伍分
1000010000.0500 壹拾亿零壹万元零伍分
101.0100 壹佰零壹元零壹分
1001.0100 壹仟零壹元零壹分
10001.0100 壹万零壹元零壹分
100001.0100 壹拾万零壹元零壹分
10000000.0100 壹仟万元零壹分
10000000001.0100 壹佰亿零壹元零壹分
100.0100 壹佰元零壹分
1000.0100 壹仟元零壹分
10000.0100 壹万元零壹分
100000.0100 壹拾万元零壹分
1000000.0100 壹佰万元零壹分
10000000.0100 壹仟万元零壹分
100000000.0100 壹亿元零壹分
1000000000.0100 壹拾亿元零壹分
10000000000.0100 壹佰亿元零壹分
ALTER FUNCTION GetChineseNum (@n_LowerMoney numeric(15,2),@v_TransType int)
RETURNS Nvarchar(4000) AS
Begin
Declare @ret Nvarchar(4000)
Declare @v_LowerStr VARCHAR(200) -- 小写金额
Declare @v_UpperPart VARCHAR(200)
Declare @v_UpperStr VARCHAR(200) -- 大写金额
Declare @i_I int
--函数作用获取在大写金额
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
+