[MSSQL]SQL 数字转换英文函数

CREATE FUNCTION [dbo].[fun_currencyENG] (@num numeric(15,2))
RETURNS varchar(400) 
AS
BEGIN

  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       ' +'ELEN    TWELEVE    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+'  ONLY')
END