--======================================================================
--
-- SQL Source File -- Created with Huangju
--
-- NAME: 金额单位转换
--
-- AUTHOR: HuangJu.MeiZhou.Gmcc
-- DATE : 2006-1-27
-- Parameters:@Letter 位
-- COMMENT: 金额单位转换
--
--======================================================================
CREATE FUNCTION GetUnit (@Letter int)
RETURNS varchar(10)
AS
BEGIN
return case @Letter
when -2 then '分'
when -1 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 '亿'
when 10 then '十'
when 11 then '佰'
end
END
--======================================================================
--
-- SQL Source File -- Created with Huangju
--
-- NAME: 数字转换为大写
--
-- AUTHOR: HuangJu.MeiZhou.Gmcc
-- DATE : 2006-1-27
-- Parameters:@Letter 数字
-- COMMENT: 数字转换为大写
--
--======================================================================
CREATE FUNCTION GetUpper (@Letter varchar(1))
RETURNS varchar(10)
AS
BEGIN
return case @Letter
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
END
--======================================================================
--
-- SQL Source File -- Created with Huangju
--
-- NAME: 金额转换大写
--
-- AUTHOR: HuangJu.MeiZhou.Gmcc
-- DATE : 2006-1-27
-- Parameters:@Letter 金额
-- COMMENT: 金额转换大写
--
--======================================================================
CREATE function GetUpperMoney( @money varchar(200))
returns varchar(400)
AS
begin
declare @a varchar(200),
@aa varchar(200), --整数部分
@ab varchar(200), --小数部分
@ac int, --级数
@ad varchar(300), --存放结果
@i int, --循环变量
@dotIndex int --小数点位置
set @a=@Money
if len(@a)<1
return ''
if ISNUMERIC(@a)<>1
return ''
set @aa=''
if PatIndex('%.%',@a)=0
set @a=@a+'.00'
set @dotIndex = PatIndex('%.%',@a)
set @aa=left(@a,@dotIndex-1)
declare @strf varchar(20)
set @strf = substring(@a,patindex('%.%',@a)+1,3)
select @strf =cast('0.'+@strf as decimal(20,2))
set @ab = right(@strf,2)
set @ad=''
---------------------------处理整数部分 开始-----------------------------------
set @i=1
set @ac=len(@aa)
while @i<=len(@aa)
begin
if substring(@aa,@i,1)='0'
begin
if substring(@aa,@i-1,1) <>'0'
set @ad= @ad +[dbo].[GetUpper](substring(@aa,@i,1))
end
else
begin
set @ad= @ad +[dbo].[GetUpper](substring(@aa,@i,1))
set @ad =@ad +[dbo].[GetUnit](@ac)
end
set @ac=@ac-1
set @i=@i+1
end
---------------------------处理整数部分 结束-----------------------------------
---------------------------处理小数部分 开始-----------------------------------
set @ad= @ad + dbo.GetUpper(left(@ab,1))
set @ad =@ad + dbo.GetUnit(-1)
set @ad= @ad + dbo.GetUpper(right(@ab,1))
set @ad =@ad + dbo.GetUnit(-2)
---------------------------处理小数部分 结束-----------------------------------
return @ad
END