Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3330
  • Last Modified:

Format Number with comma including all decimals

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
Rajkumar Gs
Asked:
Rajkumar Gs
  • 15
  • 5
  • 2
  • +2
3 Solutions
 
Paul MacDonaldDirector, Information SystemsCommented:
Try:

select convert(varchar(29), convert(money, @NumberLogins), 2)
 
0
 
Rajkumar GsSoftware EngineerAuthor Commented:

@paulmacd:, Not correct.

 Output of
select convert(varchar(29), convert(money, @NumberLogins), 2)
 is 1232323245.0000
Decimals missing
0
 
Erick37Commented:
You should declare @NumberLogins as numeric, not int.

DECLARE @NumberLogins numeric(19,6) --<< not int
SET @NumberLogins = 1232323245.12313
 
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Paul MacDonaldDirector, Information SystemsCommented:
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
 
Bryan PiggCommented:
format(**value that needs to be formated**,"#,###.##")

May have to play with the number signs and commas to get it right.
0
 
Rajkumar GsSoftware EngineerAuthor Commented:
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
 
Rajkumar GsSoftware EngineerAuthor Commented:
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
 
Rajkumar GsSoftware EngineerAuthor Commented:
@Bryan31881, Let me know the query.
0
 
Bryan PiggCommented:
I am sorry.  Thought this was a VB question not query.  Let me see what I can find.
0
 
Erick37Commented:
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
 
Rajkumar GsSoftware EngineerAuthor Commented:
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
 
Rajkumar GsSoftware EngineerAuthor Commented:
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
 
Rajkumar GsSoftware EngineerAuthor Commented:
Hi All,

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

Raj
0
 
Erick37Commented:
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
 
Rajkumar GsSoftware EngineerAuthor Commented:
Thanks Erick37,

I will check it tomorrow

Raj
0
 
SharathData EngineerCommented:
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
 
Rajkumar GsSoftware EngineerAuthor Commented:
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
 
Erick37Commented:
Unfortunately, the code provided by Sharath does not work with negative numbers if that matters...
0
 
Rajkumar GsSoftware EngineerAuthor Commented:
@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
 
Rajkumar GsSoftware EngineerAuthor Commented:
Regarding my doubt to Sharath - http:#34170089

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

Thanks
Raj
0
 
Erick37Commented:
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
 
Rajkumar GsSoftware EngineerAuthor Commented:
@Erick,
Your code http:#34170393 works perfect on all test inputs.
Thanks
Raj
0
 
Rajkumar GsSoftware EngineerAuthor Commented:
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
 
SharathData EngineerCommented:
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
 
Rajkumar GsSoftware EngineerAuthor Commented:
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
 
Rajkumar GsSoftware EngineerAuthor Commented:
Thanks Erick & Sharath.

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

Regards
Raj
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 15
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now