Solved

# Format Number with comma including all decimals

Posted on 2010-11-18
2,638 Views
``````DECLARE @NumberLogins int
``````

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
Question by:Rajkumar Gs
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 15
• 5
• 2
• +2

LVL 34

Expert Comment

ID: 34164325
Try:

0

LVL 23

Author Comment

ID: 34164376

@paulmacd:, Not correct.

Output of
is 1232323245.0000
Decimals missing
0

LVL 32

Expert Comment

ID: 34164433
You should declare @NumberLogins as numeric, not int.

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

0

LVL 34

Expert Comment

ID: 34164462
I did the same thing, but decimal.

Don't know where the delimiting commas are, though.
0

LVL 2

Expert Comment

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

ID: 34164478
Thanks Erick37

I think we are near to the solution.

``````DECLARE @NumberLogins numeric(19,6)
``````

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

0

LVL 23

Author Comment

ID: 34164487
Hi paulmacd,

``````DECLARE @NumberLogins decimal(25,4)
``````

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

LVL 23

Author Comment

ID: 34164509
@Bryan31881, Let me know the query.
0

LVL 2

Expert Comment

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

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

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
``````

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

Raj
0

LVL 23

Author Comment

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
``````

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

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

ID: 34165028
Another approach:

``````DECLARE @Value decimal(19,6)
DECLARE @FormatWhole VARCHAR(29)
DECLARE @FormatNumber VARCHAR(29)

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
``````
0

LVL 23

Author Comment

ID: 34166382
Thanks Erick37,

I will check it tomorrow

Raj
0

LVL 41

Assisted Solution

Sharath earned 125 total points
ID: 34168934
try this
``````DECLARE @NumberLogins numeric(19,6)
declare @i1 numeric(19,0),@i2 numeric(19,6),@i3 nvarchar(50)
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
``````
0

LVL 23

Author Comment

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

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

LVL 23

Author Comment

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

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

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)

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

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

LVL 23

Assisted Solution

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

*/
``````

Regards
Raj
0

LVL 41

Expert Comment

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.

0

LVL 23

Author Comment

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

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

Question has a verified solution.

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

### Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. â€¦
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backupâ€¦
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a paâ€¦
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
###### Suggested Courses
Course of the Month3 days, 18 hours left to enroll