• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3155
  • 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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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

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.

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