阿拉伯数字转英文

create function decodehundred( @hundredstring varchar ( 300 ) ) returns   varchar ( 1000 ) as begin declare   @tmp varchar ( 1000 ), @decodehundred varchar ( 1000 ) set @decodehundred = '' declare @strno1 varchar ( 1000 ) select   @strno1 = ' One       Two       Three     Four      Five      ' +                   ' Six       Seven     Eight     Nine      Ten       ' +                   ' Eleven    Twelve    Thirteen  Fourteen  Fifteen   ' +                   ' Sixteen   Seventeen Eighteen  Nineteen  ' declare @strtens1 varchar ( 1000 ) set @strtens1 =    ' Ten       Twenty    Thirty    Forty     Fifty     ' +                   ' Sixty     Seventy   Eighty    Ninety    ' declare @unit4   varchar ( 10 ) set @unit4 = ' Hundred ' if len ( @hundredstring ) > 0 and len ( @hundredstring ) <= 3   begin          if len ( @hundredstring ) = 1     begin         set @tmp = cast ( @hundredstring as int )         if @tmp <> 0               set @decodehundred =                rtrim ( substring ( @strno1 ,( @tmp - 1 ) * 10 + 1 , 10 ))     end     if len ( @hundredstring ) = 2     begin         set @tmp = cast ( @hundredstring as int )         if @tmp <> 0                if @tmp < 20                   if @tmp < 10                     set @decodehundred = ' Zero ' + rtrim ( substring ( @strno1 ,( @tmp - 1 ) * 10 + 1 , 10 ))                 else                     set @decodehundred =                     rtrim ( substring ( @strno1 ,( @tmp - 1 ) * 10 + 1 , 10 ))             else                 if cast ( right ( @hundredstring , 1 ) as int ) = 0                        set @decodehundred = rtrim ( substring ( @strtens1 ,( @tmp / 10 - 1 ) * 10 + 1 , 10 ))                 else                     set @decodehundred =   rtrim ( substring ( @strtens1 ,( @tmp / 10 - 1 ) * 10 + 1 , 10 ))                          + ' - ' + rtrim ( substring ( @strno1 ,( cast ( right ( @hundredstring , 1 ) as int ) - 1 ) * 10 + 1 , 10 ))     end     if len ( @hundredstring ) =   3     begin         if cast ( left ( @hundredstring , 1 ) as int ) <> 0             set @decodehundred = rtrim ( substring ( @strno1 ,( cast ( left ( @hundredstring , 1 ) as int ) - 1 ) * 10 + 1 , 10 ))             + ' ' + @unit4 + ' ' + dbo.decodehundred( right ( @hundredstring , 2 ))         else             set @decodehundred = dbo.decodehundred( right ( @hundredstring , 2 ))              end end return @decodehundred end go create function f_numbertostring( @number as numeric( 12 , 2 )) returns varchar ( 1000 ) as begin declare   @str varchar ( 100 ), @beforepoint varchar ( 100 ), @afterpoint varchar ( 100 ), @tmpstr varchar ( 100 ), @numbertostring varchar ( 200 ) declare   @point int , @nnumlen int declare   @nbit    integer declare   @curstring varchar ( 100 ) set @numbertostring = '' declare @unit1    varchar ( 1000 ) set @unit1 = ' Thousand  Million   Billion   Hundred   Only      Point     *          And       ' set @str = cast ( round ( @number , 2 ) as varchar ) if charindex ( ' . ' , @str ) = 0   begin     set @beforepoint = @str     set @afterpoint = '' end else begin     begin         set @beforepoint = left ( @str , charindex ( ' . ' , @str ) - 1 )         set @afterpoint = right ( @str , len ( @str ) - charindex ( ' . ' , @str ))     end            if len ( @beforepoint ) > 12     begin         set @numbertostring = ' Too big. '         return @numbertostring     end        set @str = ''     while len ( @beforepoint ) > 0     begin         set @nnumlen = len ( @beforepoint )         if @nnumlen % 3 = 0           begin             set @curstring = left ( @beforepoint , 3 )             set @beforepoint = right ( @beforepoint , @nnumlen - 3 )         end         else         begin             set @curstring = left ( @beforepoint , ( @nnumlen % 3 ))             set @beforepoint = right ( @beforepoint , @nnumlen - ( @nnumlen % 3 ))         end            set @nbit = len ( @beforepoint ) / 3         set @tmpstr = dbo.decodehundred( @curstring )         if ( @beforepoint = REPLICATE ( ' 0 ' , len ( @beforepoint )) or @nbit = 0 ) and len ( @curstring ) = 3                if cast ( left ( @curstring , 1 ) as int ) <> 0 and cast ( right ( @curstring , 2 ) as int ) <> 0                    set @tmpstr = left ( @tmpstr , charindex ( ' Hundred ' , @tmpstr ) + len ( ' Hundred ' )) + ' And ' +                           right ( @tmpstr , len ( @tmpstr ) - ( charindex ( ' Hundred ' , @tmpstr ) + 7 ))             else if cast ( left ( @curstring , 1 ) as int ) <> 0 and cast ( right ( @curstring , 2 ) as int ) = 0                    set @tmpstr = ' And ' + @tmpstr                  if @nbit = 0                set @str = ltrim ( @str + ' ' + @tmpstr )         else                         set @str = ltrim ( @str + ' ' + @tmpstr + ' ' +                        rtrim ( substring ( @unit1 ,( @nbit - 1 ) * 10 + 1 , 10 )))                  if left ( @str , 3 ) = ' And '             set @str = ltrim ( right ( @str , len ( @str ) - 3 ))         if @beforepoint = REPLICATE ( ' 0 ' , len ( @beforepoint ))              break         end     set @beforepoint = replace ( @str , ' * ' , ' ' )         if len ( @afterpoint ) > 0            set @afterpoint = ' Point ' + ' ' + dbo.decodehundred( @afterpoint ) + ' ' -- + @unit7     else         set @afterpoint = ' Only '     set @numbertostring = replace ( @beforepoint , ' Zero ' , '' ) + ' ' + @afterpoint end set @numbertostring = ltrim ( rtrim ( @numbertostring )) if right ( @numbertostring , 5 ) = ' point '    set @numbertostring = replace ( @numbertostring , ' point ' , '' ) return @numbertostring end go select dbo.f_numbertostring( 12223.01 ) /* ------------------------------------ one hundred and twenty-three （所影响的行数为 1 行） */ select dbo.f_numbertostring( 100001000 ) /* ------------------------------------ twelve thousand three hundred and one （所影响的行数为 1 行） */ drop function decodehundred,f_numbertostring