December 3, 2013 at 1:15 pm
Hi,
Declare @i float = 1223.8899
I want this to be 1,224
Declare @i float = 1234.22
I want this to be 1,234
December 3, 2013 at 1:40 pm
PSB (12/3/2013)
Hi,Declare @i float = 1223.8899
I want this to be 1,224
Declare @i float = 1234.22
I want this to be 1,234
Well you have two things going on here. First is you are rounding the values. This can be in sql with little effort. The second thing is formatting. I would strongly urge you to do your formatting in the front end instead of in sql.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 3, 2013 at 2:06 pm
This worked for me.
REPLACE(CONVERT(VARCHAR(20), CAST(ROUND(@i, 0) AS MONEY), 1), '.00', '') AS [Size]
December 3, 2013 at 5:29 pm
If you must do your formatting with SQL (hint: I agree with Sean), you might want to take a look at this article to give you some ideas:
A SQL-Based Universal Currency Formatter [/url]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 12, 2013 at 4:24 pm
+1 for Sean; yes, formatting would better be done in the outputting program. If you have to do it in SQL, here is one option for your consideration. It is not the be-all and end-all of formatting, just enough to get the job done in the few cases that there is no other way. This was code I wrote several years ago; it could be improved, but I don't currently have a need to do so.
SETANSI_NULLS ON
SETANSI_PADDING ON
IFOBJECTPROPERTY(OBJECT_ID('FormatNum'), 'IsInlineFunction') IS NOT NULL
DROP
FUNCTIONFormatNum
GO
CREATE
FUNCTIONFormatNum
(
@NumberDECIMAL(20,5),
@DecimalsINTEGER= 2
)
RETURNSVARCHAR(25)
--WITHENCRYPTION
AS
----------------------------------------------------------------------------------------------------
/*<summary>
Converts a number to a string.
</summary>*/
-- History:Author:Revision:
--2002.05.16Jonathan FaheyCreation
--2002.05.20Jonathan FaheyAdded a NULL check for the input parameter
--2005.01.27Jonathan FaheyConverted from a procedure to a function
--2005.01.28Jonathan FaheyFixed a problem that produced nulls when requesting a value
--of less than 100 with zero decimals
--2005.11.03Jonathan FaheyChanged length of return string
----------------------------------------------------------------------------------------------------
-- SELECT dbo.FormatNum(12345.987, 4) 'FormatNum'
BEGIN
DECLARE@StringVARCHAR(50),@PositionINTEGER
IF@Number IS NOT NULL
BEGIN
SET@Position = 4
SET@String = CASE @Decimals
WHEN 0 THEN CONVERT(VARCHAR(50), CONVERT(DECIMAL(20,0), @Number))
WHEN 1 THEN CONVERT(VARCHAR(50), CONVERT(DECIMAL(20,1), @Number))
WHEN 2 THEN CONVERT(VARCHAR(50), CONVERT(DECIMAL(20,2), @Number))
WHEN 3 THEN CONVERT(VARCHAR(50), CONVERT(DECIMAL(20,3), @Number))
WHEN 4 THEN CONVERT(VARCHAR(50), CONVERT(DECIMAL(20,4), @Number))
WHEN 5 THEN CONVERT(VARCHAR(50), CONVERT(DECIMAL(20,5), @Number))
ELSE CONVERT(VARCHAR(50), @Number)
END
WHILE(LEN(@String) > 3
ORCHARINDEX('.', @String) > 0
)
ANDPATINDEX('%[,.]%', LEFT(@String, CASE WHEN @Number < 0 THEN 5 ELSE 4 END)) = 0
SELECT@String = STUFF(@String,
CASE WHEN CHARINDEX('.', @String) > 0 THEN CHARINDEX('.', @String)
ELSE LEN(@String) + 1 END - (@Position - 1), 0, ','),
@Position = @Position + 4
END
RETURN@String
END
GO
GRANT EXECUTE ON FormatNum TO ???
GO
January 1, 2014 at 1:08 pm
fahey.jonathan (12/12/2013)
+1 for Sean; yes, formatting would better be done in the outputting program. If you have to do it in SQL, here is one option for your consideration. It is not the be-all and end-all of formatting, just enough to get the job done in the few cases that there is no other way. This was code I wrote several years ago; it could be improved, but I don't currently have a need to do so.
SETANSI_NULLS ON
SETANSI_PADDING ON
IFOBJECTPROPERTY(OBJECT_ID('FormatNum'), 'IsInlineFunction') IS NOT NULL
DROP
FUNCTIONFormatNum
GO
CREATE
FUNCTIONFormatNum
(
@NumberDECIMAL(20,5),
@DecimalsINTEGER= 2
)
RETURNSVARCHAR(25)
--WITHENCRYPTION
AS
----------------------------------------------------------------------------------------------------
/*<summary>
Converts a number to a string.
</summary>*/
-- History:Author:Revision:
--2002.05.16Jonathan FaheyCreation
--2002.05.20Jonathan FaheyAdded a NULL check for the input parameter
--2005.01.27Jonathan FaheyConverted from a procedure to a function
--2005.01.28Jonathan FaheyFixed a problem that produced nulls when requesting a value
--of less than 100 with zero decimals
--2005.11.03Jonathan FaheyChanged length of return string
----------------------------------------------------------------------------------------------------
-- SELECT dbo.FormatNum(12345.987, 4) 'FormatNum'
BEGIN
DECLARE@StringVARCHAR(50),@PositionINTEGER
IF@Number IS NOT NULL
BEGIN
SET@Position = 4
SET@String = CASE @Decimals
WHEN 0 THEN CONVERT(VARCHAR(50), CONVERT(DECIMAL(20,0), @Number))
WHEN 1 THEN CONVERT(VARCHAR(50), CONVERT(DECIMAL(20,1), @Number))
WHEN 2 THEN CONVERT(VARCHAR(50), CONVERT(DECIMAL(20,2), @Number))
WHEN 3 THEN CONVERT(VARCHAR(50), CONVERT(DECIMAL(20,3), @Number))
WHEN 4 THEN CONVERT(VARCHAR(50), CONVERT(DECIMAL(20,4), @Number))
WHEN 5 THEN CONVERT(VARCHAR(50), CONVERT(DECIMAL(20,5), @Number))
ELSE CONVERT(VARCHAR(50), @Number)
END
WHILE(LEN(@String) > 3
ORCHARINDEX('.', @String) > 0
)
ANDPATINDEX('%[,.]%', LEFT(@String, CASE WHEN @Number < 0 THEN 5 ELSE 4 END)) = 0
SELECT@String = STUFF(@String,
CASE WHEN CHARINDEX('.', @String) > 0 THEN CHARINDEX('.', @String)
ELSE LEN(@String) + 1 END - (@Position - 1), 0, ','),
@Position = @Position + 4
END
RETURN@String
END
GO
GRANT EXECUTE ON FormatNum TO ???
GO
Just a word of warning... if any function you build has the word BEGIN in it, you may be throwing gas on the fire of performance problems. Please see the following article.
http://www.sqlservercentral.com/articles/T-SQL/91724/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply