Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Format Number with comma including all decimals

Posted on 2010-11-18
26
Medium Priority
?
2,878 Views
Last Modified: 2012-05-10
DECLARE @NumberLogins int
SET @NumberLogins = 1232323245.12313
select convert(varchar(29), convert(money, @NumberLogins), 1)

Open in new window



Current Output is 1,232,323,245.00
Expected Output should include all the decimals whatever it is. Also Comma should be on each 3 digits.
So it should be 1,232,323,245.12313

Or is there any alternative way which is more better than this ?

Thanks
Raj
0
Comment
Question by:Rajkumar Gs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 5
  • 2
  • +2
26 Comments
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 34164325
Try:

select convert(varchar(29), convert(money, @NumberLogins), 2)
 
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 34164376

@paulmacd:, Not correct.

 Output of
select convert(varchar(29), convert(money, @NumberLogins), 2)
 is 1232323245.0000
Decimals missing
0
 
LVL 32

Expert Comment

by:Erick37
ID: 34164433
You should declare @NumberLogins as numeric, not int.

DECLARE @NumberLogins numeric(19,6) --<< not int
SET @NumberLogins = 1232323245.12313
 
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 34164462
I did the same thing, but decimal.

DECLARE @NumberLogins decimal(25,4)
SET @NumberLogins = 1232323245.12313
select convert(money, @NumberLogins, 2 )

Don't know where the delimiting commas are, though.
0
 
LVL 2

Expert Comment

by:Bryan Pigg
ID: 34164466
format(**value that needs to be formated**,"#,###.##")

May have to play with the number signs and commas to get it right.
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 34164478
Thanks Erick37

I think we are near to the solution.

DECLARE @NumberLogins numeric(19,6) 
SET @NumberLogins = 1232323245.12313
select convert(varchar(29), convert(money, @NumberLogins), 1)

Open in new window


Current output is 1,232,323,245.12
Expected output is 1,232,323,245.12313

0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 34164487
Hi paulmacd,

DECLARE @NumberLogins decimal(25,4)
SET @NumberLogins = 1232323245.12313
select convert(money, @NumberLogins, 2 ) 

Open in new window


Its output is 1232323245.1231. It should be 1,232,323,245.12313
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 34164509
@Bryan31881, Let me know the query.
0
 
LVL 2

Expert Comment

by:Bryan Pigg
ID: 34164524
I am sorry.  Thought this was a VB question not query.  Let me see what I can find.
0
 
LVL 32

Expert Comment

by:Erick37
ID: 34164538
Unfortunately Money will only display 2 decimal places with commas.  In order to show more precision with commas, we have to do it the hard way -- manually (or more Googling).

Usually, the UI will do the formatting for you and it is not done in SQL.  Is there a reason you can't do the formatting in the UI (grid, spreadsheet, etc.)?
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 34164604
ALTER FUNCTION dbo.udf_FormatNumber 
						(@Value decimal(18,6))

RETURNS varchar(50)

AS 

BEGIN 


DECLARE @FormatNumber varchar(20)

DECLARE @DECIMALPART VARCHAR(20)
SET @FormatNumber = CONVERT(varchar(20), CAST(ROUND(ABS(@Value), 0) AS money), 1) 

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

RETURN @FormatNumber + '.' + RIGHT(CAST(@Value AS VARCHAR(30)),LEN(CAST(@Value AS VARCHAR(30))) - CHARINDEX('.',  CAST(@Value AS VARCHAR(30))))

END

--SELECT dbo.udf_FormatNumber (1234567890.1234)
--Current output is 1,234,567,890.00.123400.
--It should be  1,234,567,890.00.1234

Open in new window


Can I achieve this using function. This is what I have now.

Raj
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 34164866
Ya, I got it work using this function

CREATE FUNCTION dbo.udf_FormatNumber 
						
(
	@Value VARCHAR(50), -- input value (with decimals)
	@NoOfDecimals int -- No Of decimals to round
)
RETURNS varchar(50)
AS 
BEGIN 

	DECLARE @NUMBERPART VARCHAR(50)
	DECLARE @DECIMALPART VARCHAR(20)

	SET @NUMBERPART = LEFT(@Value, CHARINDEX('.',  @Value) - 1)
	SET @DECIMALPART = RIGHT(@Value, LEN(@Value) - CHARINDEX('.',  @Value))


	DECLARE @FormatNumber varchar(20)
	SET @FormatNumber = CONVERT(varchar(20), CAST(ROUND(ABS(@NUMBERPART), 0) AS money), 1) 

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

	SET @DECIMALPART = LEFT(@DECIMALPART, @NoOfDecimals) 
	SET @DECIMALPART = @DECIMALPART + REPLICATE('0', (@NoOfDecimals - LEN(@DECIMALPART) ))

	RETURN LEFT(@FormatNumber, CHARINDEX('.',  @FormatNumber) - 1) + '.' + @DECIMALPART
END

Open in new window



SELECT dbo.udf_FormatNumber (1234567890.123456, 1)
-- Result - 1234567890.1
SELECT dbo.udf_FormatNumber (1234567890.1, 5)
-- Result - 1234567890.10000

Raj
      
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 34164879
Hi All,

My solution is complete. But I want to know,
Is there any alternate way which is better than this ?

Raj
0
 
LVL 32

Expert Comment

by:Erick37
ID: 34165028
Another approach:

DECLARE @Value decimal(19,6)
DECLARE @FormatWhole VARCHAR(29)
DECLARE @FormatNumber VARCHAR(29)

DECLARE @Sign int

select @Value = -12345678.123400

SELECT @sign = SIGN(@Value)

SELECT @Value = ABS(@value)

select @FormatWhole = convert(varchar(29), convert(money, @value), 1)
SELECT @FormatWhole = LEFT(@FormatWhole,LEN(@FormatWhole)-3)

select @FormatNumber = 
	case when @Value=floor(@Value) 
      -- deal with the integer value   
      then 
        @FormatWhole
      -- deal with the decimal value
      else
        -- deal with the integer portion and append the decimal point
        @FormatWhole + '.' + 
        -- remove the trailing zeroes
        reverse(rtrim(cast(cast(floor(reverse(cast(@Value as VARCHAR(20))))as bigint) as VARCHAR(20)))) 
    end
 
 IF @Sign = -1
 BEGIN
	SELECT @FormatNumber = '(' + @FormatNumber + ')'
 END

SELECT @FormatNumber

Open in new window

0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 34166382
Thanks Erick37,

I will check it tomorrow

Raj
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 500 total points
ID: 34168934
try this
DECLARE @NumberLogins numeric(19,6) 
SET @NumberLogins = 1234567890.1234000000000000 --1232323245.12313
declare @i1 numeric(19,0),@i2 numeric(19,6),@i3 nvarchar(50)
select @i1 = @NumberLogins, @i2 = @NumberLogins-@i1
select @i3 = CONVERT(nvarchar(50),CONVERT(money,@i1),1)
select SUBSTRING(@i3,1,LEN(@i3)-2)+
       convert(nvarchar(30),reverse(convert(int,reverse(SUBSTRING(CONVERT(nvarchar(30),@i2),3,len(CONVERT(nvarchar(30),@i2)))))))
-- output: 1,234,567,890.1234

Open in new window

0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 34170089
Thanks Sharath, Brilliant light-weight Coding!

Can you please explain what this piece of code is doing ?
convert(nvarchar(30),reverse(convert(int,reverse(SUBSTRING(CONVERT(nvarchar(30),@i2),3,len(CONVERT(nvarchar(30),@i2)))))))

Raj
0
 
LVL 32

Expert Comment

by:Erick37
ID: 34170117
Unfortunately, the code provided by Sharath does not work with negative numbers if that matters...
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 34170181
@Sharath, If you could update your code to accomodate negative values as well, it would be complete.

Dear Erick & Sharath,
Can you include this requirement as well to your coding ?

@input = 1234567890.1 & @decimals = 5
Expected Output - 1234567890.10000

Like my function works - http:#34164866

Raj
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 34170232
Regarding my doubt to Sharath - http:#34170089

I got it. That code is removing the zeros at the end of decimal

Thanks
Raj
0
 
LVL 32

Accepted Solution

by:
Erick37 earned 1500 total points
ID: 34170393
DECLARE @input decimal(19,6)
DECLARE @decimals int

select @input = 12345678.1239
select @decimals = 3

DECLARE @FormatWhole VARCHAR(29)
DECLARE @FormatNumber VARCHAR(29)
DECLARE @Sign int

SELECT @sign = SIGN(@input)
SELECT @input = ABS(ROUND(@input,@decimals))
SELECT @FormatWhole = convert(varchar(29), convert(money, @input), 1)
SELECT @FormatWhole = LEFT(@FormatWhole,LEN(@FormatWhole)-3)

SELECT @FormatNumber =
      case when @input=floor(@input)
      -- deal with the integer value  
      then
        @FormatWhole
      -- deal with the decimal value
      else
        -- deal with the integer portion and append the decimal point
        @FormatWhole + '.' +
        -- remove the trailing zeroes
        LEFT(reverse(rtrim(cast(cast(floor(reverse(cast(@input as VARCHAR(20))))AS INT) as VARCHAR(20))))+'000000',@decimals)
    end
 
 IF @Sign = -1
 BEGIN
      SELECT @FormatNumber = '(' + @FormatNumber + ')'
 END

SELECT @FormatNumber
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 34170519
@Erick,
Your code http:#34170393 works perfect on all test inputs.
Thanks
Raj
0
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 0 total points
ID: 34170532
Oops! My function is having a bug with negative numbers  - right side brace is not there!
Here is the fixed one.

CREATE FUNCTION dbo.udf_FormatNumber 
						
(
	@Value VARCHAR(50), -- input value (with decimals)
	@NoOfDecimals int -- No Of decimals to round
)
RETURNS varchar(50)
AS 
BEGIN 

	DECLARE @NUMBERPART VARCHAR(50)
	DECLARE @DECIMALPART VARCHAR(20)

	SET @NUMBERPART = LEFT(@Value, CHARINDEX('.',  @Value) - 1)
	SET @DECIMALPART = RIGHT(@Value, LEN(@Value) - CHARINDEX('.',  @Value))


	DECLARE @FormatNumber varchar(20)
	SET @FormatNumber = CONVERT(varchar(20), CAST(ROUND(ABS(@NUMBERPART), 0) AS money), 1) 



	SET @DECIMALPART = LEFT(@DECIMALPART, @NoOfDecimals) 
	SET @DECIMALPART = @DECIMALPART + REPLICATE('0', (@NoOfDecimals - LEN(@DECIMALPART) ))

	SET @FormatNumber = LEFT(@FormatNumber, CHARINDEX('.',  @FormatNumber) - 1) + '.' + @DECIMALPART

	IF SIGN(@NUMBERPART) = -1
		SET @FormatNumber = '(' + @FormatNumber + ')'
	
	RETURN @FormatNumber
END

/* TESTING

 SELECT dbo.udf_FormatNumber (-12345678.19, 6) -- > (12,345,678.190000)
 SELECT dbo.udf_FormatNumber (-12345678.19876678, 2) -- > (12,345,678.20)
 SELECT dbo.udf_FormatNumber (12345678.12345, 4) -- > 12,345,678.1235
 SELECT dbo.udf_FormatNumber (12345678.19, 6) -- > (12,345,678.190000)
 SELECT dbo.udf_FormatNumber (12345678.19876678, 2) -- > (12,345,678.20)
 SELECT dbo.udf_FormatNumber (-12345678.12345, 7) -- > 12,345,678.1235

*/

Open in new window


Regards
Raj
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34175849
Didn't get a chance to look into this question again. You can restrict the number of decimals as Erick covered in one of the posts.

Regarding negative numbers, I did not understand your requirement. If that is already fixed, no need to explain it again.

Happy to help you get this fixed.
0
 
LVL 23

Author Comment

by:Rajkumar Gs
ID: 34179898
Sorry Sharath.

As I got the solution by my own and from Erick, I planned to close the question at that time.
By my question, I meant positive & negative numbers actually, but I regret for not specifying it clearly.
About decimals, it was a requirement that came later. But even without that, I am ready to close this question by what I initially meant in the main question.

Thanks for your patience and for helping me.

Best Regards
Raj
0
 
LVL 23

Author Closing Comment

by:Rajkumar Gs
ID: 34195177
Thanks Erick & Sharath.

Eventhough Sharath's code have issues with negative number, as it worked partially, sharing points.

Regards
Raj
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question