วิธีการ Convert Money ไปเป็น Currency Format ใน SQL Server

-- T-SQL currency formatting function - scalar-valued, user-defined

CREATE FUNCTION fnFormatCurrency

               (@MoneyAmount MONEY)

RETURNS VARCHAR(24)

AS

  BEGIN

    RETURN ('$' + convert(VARCHAR,@MoneyAmount,1))

  END

GO

-- Test

SELECT TOP ( 1 ) 
   AmountYTD
,  CurrencyFormat = dbo.fnFormatCurrency(AmountYTD)

FROM     Sales



----------------------------------
AmountYTD         CurrencyFormat
----------------------------------
1764938.9859      $1,764,938.99

-- T-SQL

CREATE FUNCTION fnMoneyToEnglishNL(@Money AS money, @nl int)

    RETURNS VARCHAR(1024)

AS

BEGIN

      DECLARE @Number as BIGINT

      SET @Number = FLOOR(@Money)

      DECLARE @Below20 TABLE (ID int identity(0,1), Word varchar(32))

      DECLARE @Below100 TABLE (ID int identity(2,1), Word varchar(32))

 

      INSERT @Below20 (Word) VALUES

                        ( 'Zero'), ('One'),( 'Two' ), ( 'Three'),

                        ( 'Four' ), ( 'Five' ), ( 'Six' ), ( 'Seven' ),

                        ( 'Eight'), ( 'Nine'), ( 'Ten'), ( 'Eleven' ),

                        ( 'Twelve' ), ( 'Thirteen' ), ( 'Fourteen'),

                        ( 'Fifteen' ), ('Sixteen' ), ( 'Seventeen'),

                        ('Eighteen' ), ( 'Nineteen' )

       INSERT @Below100 VALUES ('Twenty'), ('Thirty'),('Forty'), ('Fifty'),

                               ('Sixty'), ('Seventy'), ('Eighty'), ('Ninety')

 

DECLARE @English varchar(1024) =

(

  SELECT Case

    WHEN @Number = 0 THEN  ''

    WHEN @Number BETWEEN 1 AND 19

      THEN (SELECT Word FROM @Below20 WHERE ID=@Number)

   WHEN @Number BETWEEN 20 AND 99

-- SQL Server recursive function   

     THEN  (SELECT Word FROM @Below100 WHERE ID=@Number/10)+ '-' +

           dbo.fnMoneyToEnglishNL( @Number % 10, @nl)

   WHEN @Number BETWEEN 100 AND 999  

     THEN  (dbo.fnMoneyToEnglishNL( @Number / 100, @nl))+' Hundred '+

         dbo.fnMoneyToEnglishNL( @Number % 100, @nl)

   WHEN @Number BETWEEN 1000 AND 999999  

     THEN  (dbo.fnMoneyToEnglishNL( @Number / 1000, @nl))+' Thousand '+

         dbo.fnMoneyToEnglishNL( @Number % 1000, @nl) 

   WHEN @Number BETWEEN 1000000 AND 999999999  

     THEN  (dbo.fnMoneyToEnglishNL( @Number / 1000000, @nl))+' Million '+

         dbo.fnMoneyToEnglishNL( @Number % 1000000, @nl)

   ELSE ' INVALID INPUT' END

)

SELECT @English = RTRIM(@English)

SELECT @English = RTRIM(LEFT(@English,len(@English)-1))

                 WHERE RIGHT(@English,1)='-'

IF (@@NestLevel - @nl) = 1

BEGIN

      SELECT @English = @English+' Dollars and '

      SELECT @English = @English+

      convert(varchar,convert(int,100*(@Money - @Number))) +' Cents'

END

RETURN (@English)

END

GO

------------

CREATE FUNCTION fnMoneyToEnglish(@Money AS money)

    RETURNS VARCHAR(1024)

AS

BEGIN

  RETURN (dbo.fnMoneyToEnglishNL(@Money, @@NESTLEVEL))

END

GO

SELECT NumberInEnglish=dbo.fnMoneyToEnglish ( 67)
-- Sixty-Seven Dollars and 0 Cents

SELECT NumberInEnglish=dbo.fnMoneyToEnglish ( 947.54)
-- Nine Hundred Forty-Seven Dollars and 54 Cents

SELECT NumberInEnglish=dbo.fnMoneyToEnglish ( 1266.04)
-- One Thousand Two Hundred Sixty-Six Dollars and 4 Cents

SELECT NumberInEnglish=dbo.fnMoneyToEnglish ( 4261.25)
-- Four Thousand Two Hundred Sixty-One Dollars and 25 Cents

SELECT NumberInEnglish=dbo.fnMoneyToEnglish ( 921456321.88)
-- Nine Hundred Twenty-One Million Four Hundred Fifty-Six Thousand Three Hundred Twenty-One Dollars and 88 Cents