| 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.
|
|
Questions? Suggestions? Concerns? - email me to [email protected]
Greg Dubinovsky � 2006