将小写金额转换为英文大写的SQL函数-数据库专栏,SQL Server

create function [dbo].[f_num_eng] (@num numeric(15,2))
returns varchar(400) with encryption
as
begin
–all rights reserved. pbsql
  declare @i int,@hundreds int,@tenth int,@one int
  declare @thousand int,@million int,@billion int
  declare @numbers varchar(400),@s varchar(15),@result varchar(400)
  set @numbers=one       two       three     four      five     
              +six       seven     eight     nine      ten      
              +eleven    twelve    thirteen  fourteen  fifteen  
              +sixteen   seventeen eighteen  nineteen 
              +twenty    thirty    forty     fifty    
              +sixty     seventy   eighty    ninety   
  set @s=right(000000000000000+cast(@num as varchar(15)),15)
  set @billion=cast(substring(@s,1,3) as int)–将12位整数分成4段：十亿、百万、千、百十个
  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)–百位0-9
    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)–个位0-19
    set @tenth=(case when @tenth<=1 then 0 else @tenth end)–十位0、2-9
    if (@i=1 and @billion>0 and (@million>0 or @thousand>0 or @hundreds>0)) or
       (@i=2 and (@billion>0 or @million>0) and (@thousand>0 or @hundreds>0)) or
       (@i=3 and (@billion>0 or @million>0 or @thousand>0) and (@hundreds>0))
      set @result=@result+, –百位不是0则每段之间加连接符,
    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 –百位是0则加连接符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 @tenth>0
        set @result=@result+-
      else
        if @hundreds>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 substring(@s,14,2)<>00
  begin
    set @result=@result+ point
    if substring(@s,14,1)=0
      set @result=@result+zero
    else
      set @result=@result+rtrim(substring(@numbers,cast(substring(@s,14,1) as int)*10-9,10))
    if substring(@s,15,1)<>0
      set @result=@result+ +rtrim(substring(@numbers,cast(substring(@s,15,1) as int)*10-9,10))
  end
  return(@result)
end