Solved

Format Number with comma including all decimals

Posted on 2010-11-18
26
2,552 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
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 40

Assisted Solution

by:Sharath
Sharath earned 125 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 375 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 40

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

820 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