SQL货币数字转爱尔兰语字符语句

复制代码 代码如下:Alter Function
UDF_Util_ConvertCurrencyToEnglish ( @Money Numeric(15,2), @Unit
varchar(10)=’BAHT’ ) Returns Varchar(400) As /* /// summary /// Convert
money to english /// /summary /// param name=”@Money”e.g. 1234.56 /param
/// param name=”@Unit”e.g. ‘BAHT’ /param /// returnsenglish
money/returns */ Begin DECLARE @result Varchar(400) IF @Money=0 Set
@result= ‘ZERO ‘+@Unit Else Begin Declare @i Int, @hundreds Int, @tenth
Int, @one Int, @thousand Int,@million Int,@billion Int,@numbers
Varchar(400),@s Varchar(15) Set @numbers=’ONE TWO THREE FOUR FIVE ‘
+’SIX SEVEN EIGHT NINE TEN ‘ +’ELEVEN TWELEVE THIRTEEN FOURTEEN FIFTEEN
‘ +’SIXTEEN SEVENTEEN EIGHTEEN NINETEEN ‘ +’TWENTY THIRTY FORTY FIFTY ‘
+’SIXTY SEVENTY EIGHTY NINETY ‘ Set
@s=RIGHT(‘000000000000000’+Cast(@Money As varchar(15)),15) Set
@billion=Cast(Substring(@s,1,3) As Int) Set
@million=Cast(Substring(@s,4,3) As Int) Set
@thousand=Cast(Substring(@s,7,3) As Int) Set @result=” Set @i=0 While
@i=3 BEGIN Set @hundreds=Cast(Substring(@s,@i*3+1,1) As Int) Set
@tenth=Cast(Substring(@s,@i*3+2,1) As Int) Set @one=(Case @tenth When 1
Then 10 Else 0 End)+Cast(Substring(@s,@i*3+3,1) As Int) Set
@tenth=(Case When @tenth=1 Then 0 Else @tenth End) IF (@i=3 and
(@billion0 or @million0 or @thousand0) and (@hundreds=0 and (@tenth0 or
@one0))) Set @result=@result+’ AND ‘ IF @hundreds0 Set
@result=@result+RTRIM(Substring(@numbers,@hundreds*10-9,10))+’ HUNDRED
‘ IF @tenth=2 and @tenth=9 BEGIN IF @hundreds0 Set @result=@result+’ AND
‘ Set @result=@result+RTRIM(Substring(@numbers,@tenth*10+171,10))+’ ‘
END IF @one=1 and @one=19 BEGIN IF @hundreds0 AND @tenth=0 Set
@result=@result+’ AND ‘ Set
@result=@result+RTRIM(Substring(@numbers,@one*10-9,10)) END IF @i=0 and
@billion0 Set @result=@result+’ BILLION ‘ IF @i=1 and @million0 Set
@result=@result+’ MILLION ‘ IF @i=2 and @thousand0 Set @result=@result+’
THOUSAND ‘ Set @i=@i+1 END IF(@result”) Set @result=@result+’ ‘+@Unit
IF Substring(@s,14,2)’00’ Begin Set @tenth=CAST(Substring(@s,14,1) AS
INT) Set @one=CAST(Substring(@s,15,1) AS INT) IF(@tenth=2 and @tenth=9)
Set @result=@result+RTRIM(Substring(@numbers,@tenth*10+171,10)) IF
@tenth=1 AND @one=1 and @one=19 Set @result=@result+’
‘+RTRIM(Substring(@numbers,CAST(Substring(@s,14,2) AS INT)*10-9,10))
ELSE Set @result=@result+’ ‘+RTRIM(Substring(@numbers,@one*10-9,10))
SET @result=@result+’ SATANG ‘ END ELSE Set @result=@result+’ ONLY’ END
RETURN @result END

Alter Function UDF_Util_ConvertCurrencyToEnglish      
(        
@Money Numeric(15,2),        
@Unit varchar(10)=’BAHT’        
)  Returns Varchar(400)      
As 
/*    
/// <summary>    
/// Convert money to english
/// </summary>    
/// <param name=”@Money”>e.g. 1234.56  </param>  
/// <param name=”@Unit”>e.g. ‘BAHT’  </param>      
/// <returns>english money</returns>    
*/            
Begin     
DECLARE @result Varchar(400)       
IF @Money=0    
  Set @result= ‘ZERO ‘+@Unit    
Else    
 Begin    
   Declare @i Int, @hundreds Int, @tenth Int, @one Int,  @thousand Int,@million Int,@billion Int,@numbers Varchar(400),@s Varchar(15)         
   Set @numbers=’ONE       TWO       THREE     FOUR      FIVE      ‘        
      +’SIX       SEVEN     EIGHT     NINE      TEN       ‘        
      +’ELEVEN    TWELEVE    THIRTEEN  FOURTEEN  FIFTEEN   ‘        
      +’SIXTEEN   SEVENTEEN EIGHTEEN  NINETEEN  ‘        
      +’TWENTY    THIRTY    FORTY     FIFTY     ‘        
      +’SIXTY     SEVENTY   EIGHTY    NINETY    ‘        
         
   Set @s=RIGHT(‘000000000000000’+Cast(@Money As varchar(15)),15)        
   Set @billion=Cast(Substring(@s,1,3) As Int)        
   Set @million=Cast(Substring(@s,4,3) As Int)        
   Set @thousand=Cast(Substring(@s,7,3) As Int)        
   Set @result=”        
   Set @i=0        
         
   While @i<=3        
   BEGIN        
         
      Set @hundreds=Cast(Substring(@s,@i*3+1,1) As Int)        
      Set @tenth=Cast(Substring(@s,@i*3+2,1) As Int)        
      Set @one=(Case @tenth When 1 Then 10 Else 0 End)+Cast(Substring(@s,@i*3+3,1) As Int)        
      Set @tenth=(Case When @tenth<=1 Then 0 Else @tenth End)        
         
      IF (@i=3 and (@billion>0 or @million>0 or @thousand>0) and (@hundreds=0 and (@tenth>0 or @one>0)))        
        Set @result=@result+’ AND ‘        
         
      IF @hundreds>0        
        Set @result=@result+RTRIM(Substring(@numbers,@hundreds*10-9,10))+’ HUNDRED ‘        
         
      IF @tenth>=2 and @tenth<=9        
      BEGIN        
        IF @hundreds>0        
            Set @result=@result+’ AND ‘        
        Set @result=@result+RTRIM(Substring(@numbers,@tenth*10+171,10))+’ ‘  
      END        
         
      IF @one>=1 and @one<=19        
      BEGIN        
          IF @hundreds>0 AND @tenth=0        
            Set @result=@result+’ AND ‘        
          Set @result=@result+RTRIM(Substring(@numbers,@one*10-9,10))        
      END        
         
      IF @i=0 and @billion>0        
        Set @result=@result+’ BILLION ‘        
      IF @i=1 and @million>0        
        Set @result=@result+’ MILLION ‘        
      IF @i=2 and @thousand>0        
        Set @result=@result+’ THOUSAND ‘        
      Set @i=@i+1        
END        
         
  IF(@result<>”)        
   Set @result=@result+’ ‘+@Unit        
         
   IF Substring(@s,14,2)<>’00’        
    Begin        
      Set @tenth=CAST(Substring(@s,14,1) AS INT)        
      Set @one=CAST(Substring(@s,15,1) AS INT)        
         
      IF(@tenth>=2 and @tenth<=9)        
       Set @result=@result+RTRIM(Substring(@numbers,@tenth*10+171,10))        
      IF @tenth=1 AND @one>=1 and @one<=19        
       Set @result=@result+’ ‘+RTRIM(Substring(@numbers,CAST(Substring(@s,14,2) AS INT)*10-9,10))        
      ELSE        
       Set @result=@result+’ ‘+RTRIM(Substring(@numbers,@one*10-9,10))        
         
       SET @result=@result+’ SATANG ‘        
    END        
  ELSE        
    Set @result=@result+’ ONLY’        
         
END    
RETURN @result        
END