將小寫金額轉換為英文大寫的SQL函數

來源:互聯網
上載者:User
大寫|函數|金額|小寫|轉換 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


相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。