阿拉伯数字显示成英文

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