Mobile Management For SQL Server(s!) - Siccolo - SQL Management Tool

Siccolo Development Articles - Handling Currency, SQL Development, SQL Server scripts and SQL Reporting
Google
Main
Create SQL Function To Format To Currency - display money and currency amounts in the currency format using User-Defined Function in SQL Server
 

CREATE FUNCTION format_currency (@monetary_value decimal(20,2) ) returns varchar(20)
as
begin
	declare @return_value varchar(20)
	declare @is_negative bit
	select @is_negative = case when @monetary_value<0 then 1 else 0 end

	if @is_negative = 1
		set @monetary_value = -1*@monetary_value

	set @return_value = convert(varchar, isnull(@monetary_value, 0))
	
	

	declare @before varchar(20), @after varchar(20)

	if charindex ('.', @return_value )>0 
	begin
		set @after= substring(@return_value,  charindex ('.', @return_value ), len(@return_value))	
		set @before= substring(@return_value,1,  charindex ('.', @return_value )-1)	
	end
	else
	begin
		set @before = @return_value
		set @after=''
	end
	-- after every third character:
	declare @i int
	if len(@before)>3 
	begin
		set @i = 3
		while @i>1 and @i < len(@before)
		begin
			set @before = substring(@before,1,len(@before)-@i) + ',' + right(@before,@i)
			set @i = @i + 4
		end
	end
	set @return_value = @before + @after

	if @is_negative = 1
		set @return_value = '-' + @return_value

	return @return_value 
end


	
so, for example, running select dbo.format_currency (1234), dbo.format_currency (-1234.05), results are: 1,234.00 and -1,234.05.

You can also use Convert T-SQL function, for example select $ + convert(varchar, 1234), but the result is not as "beautiful" as using user-defined function above: $1234



Side Note:
User-Defined Functions - Functions are subroutines made up of one or more Transact-SQL statements that can be used to encapsulate code for reuse.
User-defined functions are created using the CREATE FUNCTION statement, modified using the ALTER FUNCTION statement, and removed using the DROP FUNCTION statement.

CREATE FUNCTION - Creates a user-defined function, which is a saved Transact-SQL routine that returns a value. User-defined functions cannot be used to perform a set of actions that modify the global database state. User-defined functions, like system functions, can be invoked from a query. They also can be executed through an EXECUTE statement like stored procedures.
For example:
CREATE FUNCTION format_currency (@monetary_value decimal(20,2) ) returns varchar(20)

Conversion Functions - Use the conversion functions, CAST and CONVERT, to convert expressions of one data type to another data type. These conversion functions are also used to obtain a variety of special data formats.

CONVERT function - Explicitly converts an expression of one data type to another.
Usage:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )


varchar data type - variable-length character data type. Character data consists of any combination of letters, symbols, and numeric characters.

Charindex function - Returns the starting position of the specified expression in a character string.
Usage:
CHARINDEX ( expression1 , expression2 [ , start_location ] ) 


Substring function - Returns part of a character, binary, text, or image expression.
Usage:
SUBSTRING ( expression , start , length ) 
Create SQL Function To Format Number - display numbers in SQL Server
 

CREATE FUNCTION format_number (@int_value int ) returns varchar(20)
as
begin

	declare @is_negative bit
	select @is_negative = case when @int_value<0 then 1 else 0 end

	if @is_negative = 1
		set @int_value = -1*@int_value

	declare @return_value varchar(20)
	set @return_value = convert(varchar, isnull(@int_value, 0))

	declare @before varchar(20), @after varchar(20)

	set @before = @return_value
	set @after=''

	-- after every third character:
	declare @i int
	if len(@before)>3 
	begin
		set @i = 3
		while @i>1 and @i < len(@before)
		begin
			set @before = substring(@before,1,len(@before)-@i) + ',' + right(@before,@i)
			set @i = @i + 4
		end
	end
	set @return_value = @before + @after

	if @is_negative = 1
		set @return_value = '-' + @return_value

	return @return_value 
end


	
so, for example, running dbo.format_number (1234), dbo.format_number (-1234.05), results are: 1,234 and -1,234.


Article keywords: create function, user defined function, convert currency, convert money, select, convert(), varchar, charindex(), substring(), while, begin, end, sql currency format, sql format currency


Back To Articles Page

Free Mobile Management For SQL Server(s!) - Siccolo - SQL Management ToolQuestions? Suggestions? Concerns? - email me to siccolo_mobile_management@yahoo.com    Greg Dubinovsky 2006
or share your thoughts at Siccolo Blog

Web being sponsor - Mid-Atlantic Processing. Well being sponsor - Clarity MediSpa. Hairless sponsor - Clarity MediSpa Laser Hair Removal.