将数字转成英文描述(sql函数方法)

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(8802.50) DateDesc
Eight Thousand Eight Hundred And  Two Point Fifty