Format Number with comma including all decimals

Try:

select convert(varchar(29), convert(money, @NumberLogins), 2)
 

@paulmacd:, Not correct.

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

You should declare @NumberLogins as numeric, not int.

DECLARE @NumberLogins numeric(19,6) --<< not int
SET @NumberLogins = 1232323245.12313
 

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.

format(**value that needs to be formated**,"#,###.##")

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

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)

Select allOpen in new window

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

Hi paulmacd,

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

Select allOpen in new window

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

@Bryan31881, Let me know the query.

I am sorry.  Thought this was a VB question not query.  Let me see what I can find.

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.)?

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

Select allOpen in new window

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

Raj

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

Select allOpen in new window

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

Raj
      

Hi All,

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

Raj

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

Select allOpen in new window

Thanks Erick37,

I will check it tomorrow

Raj

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

Unfortunately, the code provided by Sharath does not work with negative numbers if that matters...

@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

Regarding my doubt to Sharath - http:#34170089

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

Thanks
Raj

@Erick,
Your code http:#34170393 works perfect on all test inputs.
Thanks
Raj

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.

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

Thanks Erick & Sharath.

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

Regards
Raj