T-SQL: How to format number with commas, no decimals, and parentheses for negative numbers?

I know that formatting numbers is a client-side task, but I have a special situation where I need to format a number in derived table using T-SQL.

I need -11812.53000 to appear like this: (11,812)

1) No decimal points (need to round up to the nearest dollar)
2) Commas for the thousands separator
3) Parentheses around negative numbers
4) The field *must* be numeric (can't be a string)

Again, I KNOW this isn't the proper way to use t-sql but I have to in this situation.
dbadmin78Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BodestoneCommented:
You say round up to the nearest. Do you mean round up or round to the nearest?
ie
should  1.1 and 1.9 always be 2?
should -1.1 and -.19 always be -1 or -2?
0
BodestoneCommented:
Nevertheless I have put 2 ways in here to show a sample.

The STUFFfunction takes a string as param 1(in this case the integer remaining as a string) then from postition 3 it strips out the characters for a lengths of 0 (just insert then) and replaces with the 4th param, in this case a comma.
/**
*  Sample table for testing
**/
DECLARE @stringNumbers TABLE (num VARCHAR(50))
INSERT	@stringNumbers
SELECT	'-11812.53000' UNION
SELECT	'11812.53000' UNION
SELECT	'My frog has a leaky pocket'

/**
*  2 methods due to unsurity for negative numbers
**/
SELECT	CASE 
			WHEN ISNUMERIC(num) = 0 THEN 'Invalid'
			WHEN CEILING(num) < 0 THEN '(-)' + STUFF(CAST(ABS(CEILING(num)) AS VARCHAR),3,0,',')
			ELSE STUFF(CAST(CEILING(num) AS VARCHAR),3,0,',')
		END AS NegativesUp,
		CASE 
			WHEN ISNUMERIC(num) = 0 THEN 'Invalid'
			WHEN CEILING(num) < 0 THEN '(-)' + STUFF(CAST(CEILING(ABS(num)) AS VARCHAR),3,0,',')
			ELSE STUFF(CAST(CEILING(num) AS VARCHAR),3,0,',')
		END AS NegativesDown	
FROM	@stringNumbers

Open in new window

0
Brendt HessSenior DBACommented:
I'm afraid that you cannot do what you request in SQL Server directly.  If the value presented out has thousands separators displayed, it will not be numeric - period.  Ditto for parentheses.  These should be handled by the display mechanism using your output data for presentation.  If your SQL Statement outputs data using these formats, it will be outputting character data, not numeric data.

Outputting the data formatted as specified is possible, but a bit tricky.  One point of clarification:  Rounding.  I will assume that -11812.53000 should round to -11812 and 11812.53 should round to 11813.  Given this limitation:


Declare @myvalue money

SET @MyValue = 19943.52200

SELECT SUBSTRING(CONVERT(varchar(12), ROUND(@myvalue + CASE WHEN SIGN(@myValue) < 1 THEN 0.4999 ELSE 0.0 END,0), 1), 1, LEN(CONVERT(varchar(12), ROUND(@myvalue + CASE WHEN SIGN(@myValue) < 1 THEN 0.4999 ELSE 0.0 END,0), 1))-3)

SET @MyValue = -19943.52200

SELECT SUBSTRING(CONVERT(varchar(12), ROUND(@myvalue + CASE WHEN SIGN(@myValue) < 1 THEN 0.4999 ELSE 0.0 END,0), 1), 1, LEN(CONVERT(varchar(12), ROUND(@myvalue + CASE WHEN SIGN(@myValue) < 1 THEN 0.4999 ELSE 0.0 END,0), 1))-3)

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

BodestoneCommented:
Opps, i forgot that notation thingy about the brackets was the way it was and went off on one.

As I say I am taking you to mean round up and have included the 2 methods for negative numbers. Just depends on when you apply the ceiling.

My other assumtions were that you needed to filter out non numeric values
"4) The field *must* be numeric (can't be a string)"
I took to mean that you had to ensure that the input field was numeric before attempting conversion.

bhess1 is correct of course in that the format you specify as output can only ever be considered to be numeric if it is a positive amount less than 1000.
/**
*  Sample table for testing
**/
DECLARE @stringNumbers TABLE (num VARCHAR(50))
INSERT	@stringNumbers
SELECT	'-11812.53000' UNION
SELECT	'11812.53000' UNION
SELECT	'My frog has a leaky pocket'

/**
*  2 methods due to unsurity for negative numbers
**/
SELECT	CASE 
			WHEN ISNUMERIC(num) = 0 THEN 'Invalid'
			WHEN CEILING(num) < 0 THEN '(' + STUFF(CAST(ABS(CEILING(num)) AS VARCHAR),3,0,',') + ')'
			ELSE STUFF(CAST(CEILING(num) AS VARCHAR),3,0,',')
		END AS NegativesUp,
		CASE 
			WHEN ISNUMERIC(num) = 0 THEN 'Invalid'
			WHEN CEILING(num) < 0 THEN '(' + STUFF(CAST(CEILING(ABS(num)) AS VARCHAR),3,0,',') + ')'
			ELSE STUFF(CAST(CEILING(num) AS VARCHAR),3,0,',')
		END AS NegativesDown	
FROM	@stringNumbers

Open in new window

0
SharathData EngineerCommented:

This may be helpful for you.
http://www.siccolo.com/Articles/SQLScripts/how-to-create-sql-to-format-currency.html 
But you need to tune it according to your requirement. May be Bodestone has implemented in his/her query.
0
dbadmin78Author Commented:
Thank you for your responses. I am trying them this evening and will let you know.  For the record, numbers should be rounded to the nearest dollar, with concessions for the limitations described above.
0
Anthony PerkinsCommented:
Something like this perhaps:
ALTER FUNCTION dbo.udf_FormatNumber 
						(@Value decimal(18,6))

RETURNS varchar(20)

AS 

BEGIN 

DECLARE @FormatNumber varchar(20)

SET @FormatNumber = CONVERT(varchar(20), CAST(ROUND(ABS(@Value), 0) AS money), 1) 
SET @FormatNumber = LEFT(@FormatNumber, LEN(@FormatNumber) - 3)

IF SIGN(@Value) = -1
	SET @FormatNumber = '(' + @FormatNumber + ')'

RETURN @FormatNumber

END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BodestoneCommented:
Yeah. I've added a RoundedToNearest version and some extra rows in the test data so we have greater and less than .5 versions
/**
*  Sample table for testing
**/
DECLARE @stringNumbers TABLE (id int,num VARCHAR(50))
INSERT  @stringNumbers
SELECT  1,'-11812.53000' UNION
SELECT  2,'11812.53000' UNION
SELECT  3,'-11812.25000' UNION
SELECT  4,'11812.25000' UNION
SELECT  5,'My frog has a leaky pocket'

/**
*  2 methods due to unsurity for negative numbers
**/
SELECT  id,
    CASE 
        WHEN ISNUMERIC(num) = 0 THEN 'Invalid'
        WHEN CEILING(num) < 0 THEN '(' + STUFF(CAST(ABS(CEILING(num)) AS VARCHAR),3,0,',') + ')'
        ELSE STUFF(CAST(CEILING(num) AS VARCHAR),3,0,',')
    END AS NegativesUp,
    CASE 
        WHEN ISNUMERIC(num) = 0 THEN 'Invalid'
        WHEN CEILING(num) < 0 THEN '(' + STUFF(CAST(CEILING(ABS(num)) AS VARCHAR),3,0,',') + ')'
        ELSE STUFF(CAST(CEILING(num) AS VARCHAR),3,0,',')
    END AS NegativesDown, 
      CASE 
        WHEN ISNUMERIC(num) = 0 THEN 'Invalid'
        WHEN ROUND(num,0) < 0 THEN '(' + STUFF(CAST(ABS(ROUND(num,0)) AS VARCHAR),3,0,',') + ')'
        ELSE STUFF(CAST(ROUND(num,0) AS VARCHAR),3,0,',')
    END AS RoundedToNearest
FROM    @stringNumbers
ORDER BY ID

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.