Format Currency Into a String

,

To use the script, create the function outlined by running the script attached. You can then call the function as part of a select statement to convert a money value to a formatted string.

The function will accept negative values, zero, and positive values up to 100 characters in length.

Create Function [dbo].[ufn_FormatCurrency]
( @TestValue money, @CurrencySymbol char ( 1 ) = '$' )
Returns varchar ( 100 )
/***************************************************************************************** 
* FUNCTION: dbo.ufn_FormatCurrency
* PURPOSE:  Creates a formatted string for the money value supplied
* Test:     SELECT dbo.ufn_FormatCurrency( 123456.00,'$' )
* CREATED:  Chris Kutsch (02/12/2013)
* 
* MODIFIED 
* DATE          AUTHOR     DESCRIPTION 
*----------------------------------------------------------------------------------------- 
* {date}      {developer} {brief modification description} 
* 
*****************************************************************************************/
AS
 Begin
    Declare @IsNegative bit
    If @TestValue < 0 
     Begin
        Set @IsNegative = 1
     End
    Else
     Begin
        Set @IsNegative = 0
     End
 
    SELECT @TestValue = ABS(@TestValue)
    
    Declare @DecPointPosition int
    SELECT @DecPointPosition = CHARINDEX( '.',@TestValue )

    Declare @CurrentPosition int
    Select @CurrentPosition = @DecPointPosition - 1

    Declare @strOutput varchar ( 100 )
    If @TestValue = 0
     Begin
        SELECT @strOutput = @CurrencySymbol+'  -  '
     End
    Else
     Begin
        SELECT @strOutput = ''

        Declare @CommaPlacement tinyint
        SELECT @CommaPlacement = 1

        While @CurrentPosition > 0
         Begin
         
            SELECT @strOutput = Case When @CommaPlacement % 3 = 0 And @CurrentPosition > 1 Then ',' Else '' End + SUBSTRING( Cast( @TestValue as varchar ), @CurrentPosition, 1 ) + @strOutput
            
            SELECT @CurrentPosition = @CurrentPosition - 1
            If @CommaPlacement % 3 = 0
             Begin
                Set @CommaPlacement = 1
             End
            Else
             Begin
                Set @CommaPlacement = @CommaPlacement + 1
             End

         End

        SELECT @strOutput = @CurrencySymbol + @strOutput + RIGHT( @TestValue, LEN( @TestValue ) - @DecPointPosition +1 )
     End

    If @IsNegative = 1 Set @strOutput = '(' + @strOutput + ')'
    
    Return @strOutput
 End

Rate

3 (5)

Share

Share

Rate

3 (5)