Home
  Home
Home
Search
Articles
Page Tag-Cloud
  Software
Software Tag-Cloud
Submit Patch
Building from Source
Open Source Definition
  Popular Tags
C Plus Plus
Source Code
Legacy
Class
Console
  Members
Login
Web-Email
Notable Members
  Official
Our Company
Copyright Information
Software EULA
GPL EULA
LGPL Eula
Pre-Release EULA
Privacy Policy
  Support
Make Contact
 
 
Format Currency
From the long list of missing functionality in the world of t-SQL comes the long-winded topic of currency formatting. Seriously, how can we come so far and still be missing such basic functionality? Whatever the case, this handy little function will get you to where you need to be.

Example usage: SELECT dbo.FormatCurrency(86414.5556, 2, '$')
Example Output: $86,414.56


ALTER FUNCTION FormatCurrency
(
	@Value	Decimal(38, 8),
	@DeciPlaces		INT = 0,
	@Prefix			VarChar(8) = '$'
)
RETURNS VarChar(38)
AS
BEGIN
	DECLARE @TmpValue		VarChar(38)
	DECLARE @ReturnValue	VarChar(38)
	DECLARE @IsNegative		BIT
	DECLARE @Pos			Int
	DECLARE @Before			VarChar(38)
	DECLARE @After			VarChar(38)

	SELECT @IsNegative = CASE WHEN @Value<0 THEN 1 ELSE 0 END

	IF @IsNegative = 1
	BEGIN
		SET @Value = (-1 * @Value)
	END

	SET @ReturnValue = Convert(VarChar(38), IsNull(@Value, 0))
	
	IF CharIndex ('.', @ReturnValue )>0
	BEGIN
		SET @After = SubString(@ReturnValue,  CharIndex ('.', @ReturnValue ), Len(@ReturnValue))
		SET @Before = SubString(@ReturnValue, 1,  (CharIndex ('.', @ReturnValue ) - 1))
	END ELSE BEGIN
		SET @Before = @ReturnValue
		SET @After = ''
	END

	IF Len(@After) > @DeciPlaces
	BEGIN
		SET @After = Convert(Varchar(38), Round(Convert(Float, '1.' + Right(@After, Len(@After) - 1)), @DeciPlaces))

		set @After = Right(@After, Len(@After) - 1)
	END

	IF Len(@After) = 0 AND @DeciPlaces > 0
	BEGIN
		SET @After = '.'
	END

	WHILE (Len(@After) - 1) < @DeciPlaces AND @DeciPlaces > 0
	BEGIN
		SET @After = @After + '0'
	END

	IF (Len(@Before) > 3)
	BEGIN
		SET @Pos = 3
		WHILE((@Pos > 1) AND (@Pos < Len(@Before)))
		BEGIN
			SET @Before = SubString(@Before, 1, (Len(@Before) - @Pos)) + ',' + Right(@Before, @Pos)
			SET @Pos = (@Pos + 4)
		END
	END
	SET @ReturnValue = (@Before + @After)

	SET @ReturnValue = @Prefix + @ReturnValue

	IF @IsNegative = 1
	BEGIN
		SET @ReturnValue = '-' + @ReturnValue
	END

	RETURN @ReturnValue
END


Is this code snippet, product or advice warrantied against ill-effect and/or technical malaise? No. No it's not! Not expressed - Not implied - not at all.



Tags:
 Currency    Formatting    Function    Numeric    Snippet    SQL Server  

Created by Josh Patterson on 2/9/2013, last modified by Josh Patterson on 2/9/2013

No comments currently exists for this page. Why don't you add one?
First Previous Next Last 

Login or signup to leave a comment.
 
Copyright © 2017 NetworkDLS.
All rights reserved.
 
Privacy Policy | Our Company | Contact