数字转英文(1200=> one thousand two HUNDRED)Sql函数

CREATE function FunAmtEng(@Amt numeric(10,2))
RETURNS  varchar(200)
as
begin
declare @strAmt varchar(10)
declare @str varchar(200)
declare @tempS varchar(200)
declare @i int
declare @j int
---------
set @str=''
set @i=1
set @j=1  
set @strAmt=right(cast(round(10000000+@Amt,2) as varchar),9)
------
if  cast(left(@strAmt,6) as int)>0
begin
         while @j<3
         begin
                  if (@j=1)
                               set @tempS=left(@strAmt,3)
                  else
                               set @tempS=substring(@strAmt,4,3)
---------------------------------------------------------------------
               if cast(@tempS as int)>0
               begin
                       while @i<3
                       begin
                               if @i=1
                                    begin
                                              set @str=dbo.FunFormatAmtEng(left(@tempS,1) ,@str ,1)
                                              if cast(left(@tempS,1) as int)>0
                                                 set @str=@str+' HUNDRED AND'
                                     end
                                else if @i=2
                                         begin
                                                  if substring(@tempS,@i,1)='0'
                                                      set @str=dbo.FunFormatAmtEng(substring(@tempS,@i+1,1) ,@str ,1)
                                                      else if substring(@tempS,@i,1)='1'
                                                             set @str=dbo.FunFormatAmtEng(substring(@tempS,@i,2) ,@str ,2)
                                                            else begin
                                                                              set @str=dbo.FunFormatAmtEng(substring(@tempS,@i,1) ,@str ,3)
                                                                              set @str=dbo.FunFormatAmtEng(substring(@tempS,@i+1,1) ,@str ,1)
                                                                    end
                                                                      
 
                                         end  
                               set @i=@i+1
                       end
               end
---------------------------------------------------------------------
        if (@j = 1) and (cast(@tempS as int) > 0)
          set  @str= @str + ' THOUSAND'

         set @j=@j+1
         set @i=1
         end
        set  @str= @str + ' DOLLARS AND ' +right(@strAmt,2) + ' CENTS'       
end
else
set  @str= @str + ' ZERO DOLLARS AND ' +right(@strAmt,2) + ' CENTS'
------

return @str
end

===========================================================

CREATE function FunFormatAmtEng(@strAmt varchar(9),@str varchar(200),@fg int)
RETURNS  varchar(200)
as
begin  
   set @str=@str+ case  @fg when  1 then
    case  substring(@strAmt,1,1)  when '1' then ' ONE'
                                                                                                         when '2' then ' TWO'
                                                                                                         when '3' then ' THREE'
                                                                                                         when '4' then ' FOUR'
                                                                                                         when '5' then ' FIVE'
                                                                                                         when '6' then ' SIX'
                                                                                                         when '7' then ' SEVEN'
                                                                                                         when '8' then ' EIGHT'
                                                                                                         when '9' then ' NINE'
                                                                                                                 else ''
                                                                     end      
-----------------------------------------------------------------------------------------------------------------                                                            
                                             when  2 then 
   case  substring(@strAmt,2,1)   when '0' then  '  TEN'
                                            when '1' then  '  ELEVEN'
                                                                                                         when '2' then  '  TWELVE'
                                                                                                         when '3' then  '  THIRTEEN' 
                                                                                                         when '4' then  '  FOURTEEN'
                                                                                                         when '5' then  '  FIFTEEN'
                                                                                                         when '6' then  '  SIXTEEN'
                                                                                                         when '7' then  '  SEVENTEEN'
                                                                                                         when '8' then  '  EIGHTEEN'
                                                                                                         when '9' then  '  NINETEEN'
                                                                                                         else ''
                                                                     end                                                       
-----------------------------------------------------------------------------------------------------------------    
                                             when  3  then   
   case  substring(@strAmt,1,1)  when '2' then '  TWENTY'
                                                                                                         when '3' then '  THIRTY'
                                                                                                         when '4' then '  FORTY'
                                                                                                         when '5' then '  FIFTY'
                                                                                                         when '6' then '  SIXTY'
                                                                                                         when '7' then '  SEVENTY'
                                                                                                         when '8' then '  EIGHTY'
                                                                                                         when '9' then '  NINETY'
                                                                                                         else ''
                                                                     end                                                            
----------------------------------------------------------------------------------------------------------------- 
                             end
                                   
      
       return isnull(@str,'')
end

调用：select dbo.FunAmtEng(1200)