• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5639
  • Last Modified:

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.
0
dbadmin78
Asked:
dbadmin78
2 Solutions
 
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
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
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
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now