0 Replies - 1554 Views - Last Post: 19 April 2008 - 10:09 PM

#1 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

User defined function to format currency in MSSQL

Posted 19 April 2008 - 10:09 PM

Description: Pass the function the value you wish to have formattedThis is a snippet to format currency values. This function takes a decimal data type and returns a varchar formatted as currency
ALTER FUNCTION dbo.FormatCurrency (@value decimal(20,2),@dollar_sign BIT = NULL) 
RETURNS VARCHAR(20)
AS
BEGIN
	DECLARE @results VARCHAR(20)
	DECLARE @negative BIT
	SELECT @negative = CASE WHEN @value < 0 THEN 1 ELSE 0 END
	IF @dollar_sign IS NULL SET @dollar_sign = 0
	IF @negative = 1 SET @value = -1 * @value

	SET @results = CONVERT(VARCHAR, ISNULL(@value, 0))	
	
	DECLARE @before VARCHAR(20), @after VARCHAR(20)

	IF CHARINDEX ('.', @results )>0 
		BEGIN
			SET @after = SUBSTRING(@results,  CHARINDEX ('.', @results ), LEN(@results))	
			SET @before = SUBSTRING(@results,1,  CHARINDEX ('.', @results )-1)	
		END
	ELSE
		BEGIN
			SET @before = @results
			SET @after = ''
		END
	-- after every third character:
	DECLARE @counter INT
	IF LEN(@before) > 3 
		BEGIN
			SET @counter = 3
			while @counter > 1 AND @counter < LEN(@before)
			BEGIN
				SET @before = SUBSTRING(@before, 1 ,LEN(@before) - @counter) + ',' + RIGHT(@before,@counter)
				SET @counter = @counter + 4
			END
		END
		SET @results = @before + @after
	
		IF @negative = 1 SET @results = '-' + @results

	RETURN CASE WHEN @dollar_sign = 1 THEN '$' + @results ELSE @results END
END


Is This A Good Question/Topic? 0
  • +

Page 1 of 1