create function dbo.TCN
(@x varchar(50)) returns varchar(100)
begin
declare @r varchar(100),@zs varchar(50),@xs varchar(50)
declare @a int,@b int
if @x='' or len(@x)=0
begin
select @r=''
return @r
end
declare @gc table(i tinyint,d varchar(5))
declare @hs table(i tinyint,d varchar(5))
insert into @gc(i,d)
select 1,'壹' union all
select 2,'贰' union all
select 3,'叁' union all
select 4,'肆' union all
select 5,'伍' union all
select 6,'陆' union all
select 7,'柒' union all
select 8,'捌' union all
select 9,'玖'
insert into @hs(i,d)
select 1,'' union all
select 2,'拾' union all
select 3,'佰' union all
select 4,'仟' union all
select 5,'万' union all
select 6,'拾' union all
select 7,'佰' union all
select 8,'仟' union all
select 9,'亿' union all
select 10,'拾'
select @x=case when charindex('.',@x,1)=0 then @x+'.00'
when (len(@x)-charindex('.',@x,1))=1 then @x+'0'
else @x end
select @zs=left(@x,charindex('.',@x,1)-1),
@xs=right(@x,len(@x)-charindex('.',@x,1)),
@r='',
@a=1,
@b=len(@zs)
while(@a<=len(@zs))
begin
if substring(@zs,@a,1)='0' and right(@r,1)<>'零'
begin
select @r=@r+'零'
end
else
begin
If substring(@zs,@a,1)<>'0'
begin
select @r=@r+isnull((select top 1 d from @gc where i=cast(substring(@zs,@a,1) as tinyint)),'')
select @r=@r+isnull((select top 1 d from @hs where i=cast(@b as tinyint)),'')
end
end
if len(@zs)>=6
begin
if left(right(@zs,5),1)='0' and @b=5
begin
select @r=left(@r,len(@r)-1)
select @r=@r+'万'
end
end
select @a=@a+1,@b=@b-1
end
if @xs='00'
begin
select @r=replace(@r+'元整','零元','元')
return @r
end
select @r=replace(@r+'元','零元','元')
if substring(@xs,1,1)<>'0'
begin
select @r=@r+isnull((select top 1 d from @gc where i=cast(substring(@xs,1,1) as tinyint)),'')+'角'
end
if substring(@xs,2,1)<>'0'
begin
select @r=@r+isnull((select top 1 d from @gc where i=cast(substring(@xs,2,1) as tinyint)),'')+'分'
end
else
begin
select @r=@r+'整'
end
return @r
end
-- 测试
select dbo.TCN('1335.32') 'x1',dbo.TCN('533100.3') 'x2'
/*
x1 x2
------------------------------ ------------------------------
壹仟叁佰叁拾伍元叁角贰分 伍拾叁万叁仟壹佰元叁角整
(1 row(s) affected)
*/
create function dbo.TCN
(@x varchar(50)) return
阅读背景：
TSQL 大写金额转换函数
来源：互联网
分享到：
