mmoney
200.000000
870.000000
1900.000000
1600.000000
1100.000000
900.000000
1700.000000转换后的形式贰零零
捌柒零
壹玖零零
....
...
..
.希望哪位朋友有空 帮我写下SQL语句 谢谢
200.000000
870.000000
1900.000000
1600.000000
1100.000000
900.000000
1700.000000转换后的形式贰零零
捌柒零
壹玖零零
....
...
..
.希望哪位朋友有空 帮我写下SQL语句 谢谢
作者:([email protected])
版本:1.0
创建时间:20020227
修改时间:
功能:小写金额转换成大写
参数:n_LowerMoney 小写金额
v_TransType 种类 -- 1: directly translate, 0: read it in words
输出:大写金额
********************************************************/
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 1900.000000,1,@ret output
select @ret
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
壹仟玖佰零拾零元零角零分(1 行受影响)
RETURNS varchar(100) WITH ENCRYPTION
AS
BEGIN
--版权所有:[email protected]
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
SQL SERVER函数将人民币数字转换成大写形式
闲着也是闲着,欢迎大家多提宝贵意见--阿拉伯数字转中文大写数字
Create function NToCC(@n int)
returns varchar(2)
as
begindeclare @s varchar(2)if @n=0
set @s=''零''
if @n=1
set @s=''壹''
if @n=2
set @s=''贰''
if @n=3
set @s=''叁''
if @n=4
set @s=''肆''
if @n=5
set @s=''伍''
if @n=6
set @s=''陆''
if @n=7
set @s=''柒''
if @n=8
set @s=''捌''
if @n=9
set @s=''玖''return @s end
-----可支持到上亿
Create function numbertoChineseBigString(@m money)
returns varchar(255)
begin
declare @i varchar(255)
declare @f varchar(255)
declare @r varchar(255)
declare @s varchar(255)set @s=convert(varchar(255),@m)
if charindex(''.'',@s)>0
begin
set @i=substring(@s,1,charindex(''.'',@s)-1)
set @f=substring(@s,charindex(''.'',@s)+1,len(@s))
end
else
set @i=@sset @r=''''
declare @k int
set @k=len(@i)
while @k>=1
begin--最多9位
if @k=9
set @r=@r+
dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))
--@i
+''亿''
if @k=8
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''千''
if @k=7
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''佰''if @k=6
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''拾''if @k=5
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''万''if @k=4
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''千''if @k=3
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''佰''if @k=2
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''拾''if @k=1
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+''元''
set @k=@k-1endif convert(float,@f)=0
set @r=@r+''整''
else
beginif substring(@s,1,1)>''''
set @r=@r+dbo.NToCC(convert(int,substring(@f,1,1)))+''角''if substring(@s,2,1)>''''
set @r=@r+dbo.NToCC(convert(int,substring(@f,2,1)))+''分''endset @r=replace(@r,''零千零佰零拾'',''零'')
set @r=replace(@r,''零佰零拾'',''零'')
set @r=replace(@r,''零千零佰'',''零'')
set @r=replace(@r,''零拾'',''零'')
set @r=replace(@r,''零千'',''零'')
set @r=replace(@r,''零佰'',''零'')
set @r=replace(@r,''拾零万'',''拾万'')
return @rend-- select dbo.numbertoChineseBigString(100102)文章出处:http://www.diybl.com/course/7_databases/sql/sqlServer/2008224/101116.htmlCREATE 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 intset @v_LowerStr = LTRIM(RTRIM(ROUND(@n_LowerMoney,2))) --四舍五入为指定的精度并删除数据左右空格set @i_I = 1set @v_UpperStr = ''while ( @i_I <= len(@v_LowerStr))beginselect @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_IWHEN 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 ''ENDset @v_UpperStr = @v_UpperPart + @v_UpperStrset @i_I = @i_I + 1endif ( 0 = @v_TransType)beginset @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))beginset @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))endif ( '零' = substring(@v_UpperStr,1,1))beginset @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))endif ( '角' = substring(@v_UpperStr,1,1))beginset @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))endif ( '分' = substring(@v_UpperStr,1,1))beginset @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))endif ('整' = substring(@v_UpperStr,1,1))beginset @v_UpperStr = '零元整'endreturn @v_UpperStrEND
returns varchar(2)
as
begindeclare @s varchar(2)if @n=0
set @s=''零''
if @n=1
set @s=''壹''
if @n=2
set @s=''贰''
if @n=3
set @s=''叁''
if @n=4
set @s=''肆''
if @n=5
set @s=''伍''
if @n=6
set @s=''陆''
if @n=7
set @s=''柒''
if @n=8
set @s=''捌''
if @n=9
set @s=''玖''return @s end
为什么执行这句话 我的数据 都成NULL 或者 零
200.000000
870.000000
1900.000000
....
..
.转换后的形式mmoney
贰零零
捌柒零
壹玖零零
....
insert into aaa
select 120
union all
select 210
select replace(replace(replace(a,0,'零'),1,'壹'),2,'贰') from aaa
drop table aaa 2 record(s) affected column1
----------
壹贰零
贰壹零 2 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A (mmoney numeric(10,6))
insert into #A
select 200.000000 union all
select 870.000000 union all
select 1900.000000 union all
select 1600.000000 union all
select 1100.000000 union all
select 900.000000 union all
select 1700.000000
ALTER FUNCTION GET_UPPER(@mmoney numeric(10,6))
RETURNS NVARCHAR(30)
AS
BEGIN
DECLARE @S NVARCHAR(30),@RESULT NVARCHAR(40)
SET @RESULT=LTRIM(CONVERT(INT,@mmoney))
SET @S=REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@RESULT,'9','玖'),'8','捌'),'7','柒'),'6','陆'),'5','伍'),'4','肆'),'3','叁'),'2','贰'),'0','零'),'1','壹')
RETURN @S
END
select DBO.GET_UPPER(mmoney) from #A
(所影响的行数为 7 行)
------------------------------
贰零零
捌柒零
壹玖零零
壹陆零零
壹壹零零
玖零零
壹柒零零(所影响的行数为 7 行)
IF EXISTS (select 1 from sys.objects where name = 'f_num_chn' and type = 'FN')
DROP FUNCTION [dbo].[f_num_chn]
GO
CREATE FUNCTION [dbo].[f_num_chn] (@num money)
RETURNS varchar(20) WITH ENCRYPTION
AS
BEGIN DECLARE @i int
DECLARE @strNum varchar(20)
DECLARE @result varchar(20)SET @strNum = convert(varchar,convert(int,@num))
SET @i = LEN(@strNum)
SET @result = ''WHILE @i > 0
BEGIN
SELECT @result=
CASE SUBSTRING(@strNum,@i,1)
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 + @result
SET @i = @i - 1
END
RETURN @result
END
GO--测试
if object_id('tempdb.dbo.#test') is not null drop table #test
create table #test (mmoney money)
insert into #test
SELECT 200.000000 UNION ALL
SELECT 870.000000 UNION ALL
SELECT 1900.000000 UNION ALL
SELECT 1600.000000 UNION ALL
SELECT 1100.000000 UNION ALL
SELECT 900.000000 UNION ALL
SELECT 1700.000000 UNION ALL
SELECT 1234567890.000000
GO
SELECT dbo.f_num_chn(mmoney) mmoney FROM #test--结果
mmoney
--------------------
贰零零
捌柒零
壹玖零零
壹陆零零
壹壹零零
玖零零
壹柒零零
壹贰叁肆伍陆柒捌玖零(8 row(s) affected)
CREATE function fn_get_capital(@ll_str money)
returns varchar(200)
as
begin
--declare @str0 varchar(10)
declare @str1 varchar(20)
declare @str2 varchar(30)
--declare @str3 varchar(5)
declare @ll_dec integer
declare @ll_int bigint
declare @i integer declare @ls_left varchar(200)
declare @ls_right varchar(200)
declare @ls_tmp varchar(200)
--初始化 set @ls_left=''
set @ls_right='' set @i=1 --set @str0='1234567890'
set @str1='壹贰叁肆伍陆柒捌玖零'
set @str2='元拾佰仟万拾佰仟亿拾佰仟万'
--set @str3='角分'
set @ll_dec=cast((@ll_str - round(@ll_str,0,1)) as decimal(9,2)) *100
set @ll_int=cast(round(@ll_str,0,1) as bigint)
--小数部分
if @ll_dec=0
begin
set @ls_right='整'
end
if @ll_dec>0 and @ll_dec<=9
begin
set @ls_right='零'+substring(@str1,@ll_dec,1)+'分'
end
if @ll_dec>9
begin
set @ls_right=substring(@str1,@ll_dec/10,1)+'角'
if @ll_dec%10>0
begin
set @ls_right=@ls_right+substring(@str1,@ll_dec%10,1)+'分'
end
end
--整数部分
while @ll_int>0
begin
if @ll_int%10=0
begin
if @i=1 or @i=5 or @i=9
begin
set @ls_left='零'+substring(@str2,@i,1)+@ls_left
end
else
begin
set @ls_left='零'+@ls_left
end
end
else
begin
set @ls_left=substring(@str1,@ll_int%10,1)+substring(@str2,@i,1)+@ls_left
end set @ll_int=@ll_int/10
set @i=@i+1
end set @ls_tmp=@ls_left+@ls_right while @i>0
begin
select @ls_tmp=replace(@ls_tmp,'零零','零')
set @i=@i-1
end select @ls_tmp=replace(@ls_tmp,'零元','元')
select @ls_tmp=replace(@ls_tmp,'零万','万')
select @ls_tmp=replace(@ls_tmp,'零亿','亿') return @ls_tmpend
--select dbo.wf_get_capital(200000010500)贰仟亿零壹万零伍佰元整