阿拉伯数字转英文

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