Solved

Format Number with comma including all decimals

Posted on 2010-11-18
26
2,472 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 33

Expert Comment

by:paulmacd
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
 
LVL 33

Expert Comment

by:paulmacd
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now