TSQL 大写金额转换函数

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)
*/