March 13, 2013 at 5:04 pm
I really like sql server, but one of the things that drives me crazy is formatting columns. The numbers in the databases that I pull from are usually numeric and I really need commas to help me read the numbers. I found the code below that will convert the numeric into numbers with columns. Since I use this all of the time, I would like to create a UDF. I have never done this before, but have found many examples.
This is what I would like the UDF to do:
REPLACE(CONVERT(varchar(20), (CAST(SUM(@numtocomma ) AS money)), 1), '.00', '')
So far this:
CREATE FUNCTION commainsert
( @numtocomma numeric(38,6))
RETURNS money
AS
BEGIN
declare @monval money
set @monval=REPLACE(CONVERT(varchar(20), (CAST(SUM(@numtocomma ) AS money)), 1), '.00', '')
return @monval
END
select dbo.commainsert(10201.21) as Number
yields this:
Number
10201.21
Would someone please tell me where I went wrong?
Thanks
March 13, 2013 at 5:11 pm
Your client application should do all the formatting, not a database server!
March 13, 2013 at 10:35 pm
I agree, this task is better left to a client application but you are not the first person to ever want this to be delivered through the database engine. SQL Server 2012 has this type of functionality built in: FORMAT (Transact-SQL) - SQL Server 2012
Try this:
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.commainsert')
AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ) )
DROP FUNCTION dbo.commainsert;
GO
CREATE FUNCTION dbo.commainsert
(
@numtocomma NUMERIC(38, 6)
)
RETURNS VARCHAR(50)
AS
BEGIN
RETURN REPLACE(CONVERT(VARCHAR(50), CAST(@numtocomma AS MONEY), 1), '.00', '');
END
GO
SELECT dbo.commainsert(10201.21) AS Number;
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 13, 2013 at 11:15 pm
You might be able to make use of this Currency Formatter Function[/url].
This will let you enter values like this:
SELECT * FROM tvfUniversalCurrencyFormatter(123456789.23,'US',0)
with result: 123,456,789.23
or SELECT * FROM tvfUniversalCurrencyFormatter(123456789.23,'GB',1)
with result: £123,456,789.23
or SELECT * FROM tvfUniversalCurrencyFormatter(123456789.23,'ID',2)
with result: IDR 123.456.789,23
The possibilities are almost endless...with a little effort you can add any mask you want for any purpose.
March 14, 2013 at 9:04 am
Thank you opc.three. I changed my code to look like yours and it works. There are a couple flaws (in the results, not your code) such as a varchar is returned so the numbers in the column line up on the left which looks goofy and you cannot sum the numbers. These are flaws that I can live with though.
Thank you Mr. Willis, someday my company will probably upgrade to 2012 but I will have to live with what I have until then.
March 14, 2013 at 9:07 am
General rule - format is a display problem, should be done in the presentation layer of the application, not the database server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 14, 2013 at 10:40 am
cljolly (3/14/2013)
Thank you opc.three. I changed my code to look like yours and it works. There are a couple flaws (in the results, not your code) such as a varchar is returned so the numbers in the column line up on the left which looks goofy and you cannot sum the numbers. These are flaws that I can live with though.
That's the price you pay for working with numbers when they are stored as strings and is one of many reasons why it's best to offload this type of work into the application layer where the presentation can be more easily manipulated. SQL Server is optimized for storing and retrieving data, not presenting it to an end user.
Thank you Mr. Willis, someday my company will probably upgrade to 2012 but I will have to live with what I have until then.
I think I was the one that mentioned 2012. The article Steven linked to will actually be a better performing option than the one you have. The article is a little advanced, but if you're working with large resultsets it would be worth your while to look into implementing the table-valued function shown in the article and use that instead of the scalar-valued function you are currently using.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 14, 2013 at 11:02 am
My bad. I should have given you credit for informing me about sql server 2012. I am at the mercy of my company on what versions of software we use. Eventually, I should get 2012.
March 14, 2013 at 1:24 pm
Not a credit thing...just wanted to highlight that Steven was pointing towards a solid solution in case you crossed the posts up and missed the link he posted.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 14, 2013 at 1:31 pm
I wish all the responses to my thread were as kind and helpful as yours. I got two responses telling me I was breaking all laws of nature and physics by asking sql server to format data. I really only need to be told once, although I guess it's important enough that Microsoft is adding the function to 2012.
March 14, 2013 at 9:23 pm
cljolly (3/14/2013)
I wish all the responses to my thread were as kind and helpful as yours. I got two responses telling me I was breaking all laws of nature and physics by asking sql server to format data. I really only need to be told once, although I guess it's important enough that Microsoft is adding the function to 2012.
Heh... they actually have your best interest at heart.
Shifting gears back to formatting, what are you going to do with the output of this? I ask because if it's to be included in an email, we do can a couple of really neat formatting tricks that you might not think possible. We can even make the numbers right align like you'd expect them to. I do such tricks all the time with my server morning reports because I don't have a front-end to work with and I think that SSRS is a 4 letter word. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2013 at 7:14 am
I want to use Microsoft Snipping tool and do a quick copy and paste.
March 17, 2013 at 2:35 pm
cljolly (3/16/2013)
I want to use Microsoft Snipping tool and do a quick copy and paste.
You'll have to tell me what you want to do when there are too many columns or rows to fit the screen.
In the meantime, scalar functions tend to be a about 7 times slower than iSFs, which stands for "Inline Scalar Function", and are really iTVFs that return just a scalar value. Please see the rollowing URL for more on that subject.
http://www.sqlservercentral.com/articles/T-SQL/91724/
With performance in mind, here's a formatting function. Details are in the code including how to use it.
CREATE FUNCTION dbo.CommaFormatNumber
/***************************************************************************************************
Purpose:
To convert any numeric value that can be converted to the MONEY data to a right aligned
string formatted with commas and rounded or truncated to the desired number of decimal
places.
Programmer notes:
1. Notes on inputs
@SomeNumber : Must be implicitly convertable to the MONEY datatype.
@ColWidth : Must be large enough to hold the final formatted data.
@DecPlaces : Must be from 0 to 4 decimal places.
@Suffix : Can be any varchar up to 25 characters. May be NULL.
@Truncate : If NULL or 0, then rounds to # of desired decimal places.
: If < 0 or > 0, then truncates to the # of desired decimal places.
2. If any errors occur, then will produce an error with the reason for the error rather
than returning a string of astericks as many other programs do.
SELECT pod.DueDate,
Quantity = CONVERT(CHAR( 8),ca1.FormattedNumber),
UnitPrice = CONVERT(CHAR(10),ca2.FormattedNumber),
LineTotal = CONVERT(CHAR(14),ca3.FormattedNumber)
FROM AdventureWorks.Purchasing.PurchaseOrderDetail pod
CROSS APPLY dbo.CommaFormatNumber(pod.OrderQty ,8,0,NULL,NULL)ca1
CROSS APPLY dbo.CommaFormatNumber(pod.UnitPrice,10,4,'ea',0)ca2
CROSS APPLY dbo.CommaFormatNumber(pod.OrderQty*UnitPrice,14,2,NULL,NULL)ca3
Usage:
--===== Basic Syntax
SELECT FormattedNumber
FROM dbo.CommaFormatNumber(@SomeNumber,@ColWidth,@DecPlaces,@Suffix,@Truncate)
;
--===== Example of formatting multiple columns from a table
SELECT pod.DueDate,
Quantity = ca1.FormattedNumber,
UnitPrice = ca2.FormattedNumber,
LintTotal = ca3.FormattedNumber
FROM AdventureWorks.Purchasing.PurchaseOrderDetail pod
CROSS APPLY dbo.CommaFormatNumber(pod.OrderQty ,8,0,NULL,NULL)ca1
CROSS APPLY dbo.CommaFormatNumber(pod.UnitPrice,10,4,'ea',0)ca2
CROSS APPLY dbo.CommaFormatNumber(pod.OrderQty*UnitPrice,14,2,NULL,NULL)ca3
;
--===== Example of returning data to the TEXT screen (can use with SELECT/INTO).
-- If returning the data to the screen in the text mode or using SELECT/INTO to build
-- the proper width columns, you'll have to add a bit of extra code to control
-- the actual column width like this...
SELECT pod.DueDate,
Quantity = CONVERT(CHAR( 8),ca1.FormattedNumber),
UnitPrice = CONVERT(CHAR(10),ca2.FormattedNumber),
LineTotal = CONVERT(CHAR(14),ca3.FormattedNumber)
FROM AdventureWorks.Purchasing.PurchaseOrderDetail pod
CROSS APPLY dbo.CommaFormatNumber(pod.OrderQty ,8,0,NULL,NULL)ca1
CROSS APPLY dbo.CommaFormatNumber(pod.UnitPrice,10,4,'ea',0)ca2
CROSS APPLY dbo.CommaFormatNumber(pod.OrderQty*UnitPrice,14,2,NULL,NULL)ca3
;
Revision History:
Rev 00 - 17 Mar 2013 - Jeff Moden
- Initial creation to answer problem at the following URL.
http://www.sqlservercentral.com/Forums/Topic1430680-1292-1.aspx#bm1431888
***************************************************************************************************/
--===== Declare the IO for this function
(
@SomeNumber MONEY,
@ColWidth TINYINT,
@DecPlaces TINYINT,
@Suffix VARCHAR(8000),
@Truncate TINYINT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH cteRoundedValue AS
( --=== Round or truncate the value to the desired number of decimal places.
SELECT RoundedMoney = ROUND(@SomeNumber,@DecPlaces,@Truncate)
),
ctePreFormattedStrings AS
( --==== Add the commas and split out the decimal data
SELECT WithCommas = CONVERT(VARCHAR(25),RoundedMoney,1),
Decimals = RIGHT(CONVERT(VARCHAR(25),RoundedMoney,2),5)
FROM cteRoundedValue
),
cteSimpleFormattedString AS
( --=== Split out the formatted integer and add the desired number of decimal places
SELECT SimpleFormattedString =
SUBSTRING(WithCommas,1,CHARINDEX('.',WithCommas)-1)
+ CASE WHEN @DecPlaces > 0 THEN LEFT(Decimals,@DecPlaces+1) ELSE '' END
+ ISNULL(' ' + @Suffix,'')
FROM ctePreFormattedStrings
) --=== Return the right aligned string with error checking
SELECT FormattedNumber = RIGHT(SPACE(@ColWidth) + SimpleFormattedString, @ColWidth)
FROM cteSimpleFormattedString
WHERE 1 = CASE
WHEN @DecPlaces NOT BETWEEN 0 AND 4
THEN CONVERT(INT,'Number of decimal places must be between 0 and 4.')
WHEN LEN(SimpleFormattedString) > @ColWidth
THEN CONVERT(INT,QUOTENAME(SimpleFormattedString,'"') + ' is too big for column width.')
ELSE 1
END
;
GO
All of that, especially the right justification and control of the number of decimal points, may be overkill in which case opc.three's code should do it for you.
If it's something you need to do a whole lot, as the others have stated, T-SQL is a bit tough on formatting numbers. If you have the wherewithall for it, you might want to consider writing a CLR (or have someone right it for you).
Considering that you're only interested in using the snipping tool for all of this, I'd be curious what the output actually is and why you're settling for a graphic instead of columnized data.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply