February 13, 2003 at 5:55 pm
I have a floating point number in a table that I want to display in a query as "#,###" (commas every three digits, no decimal place).
The following vomitrocious code is all I can figure out:
SELECT left(convert(varchar, (convert(money, FieldName)), 3),len(convert(varchar, (convert(money, FieldName)), 3))-3) FROM TableName
Surely there's a better way in SQL to format? CAST and CONVERT are extremely limited. What is a general way to format numbers?
February 17, 2003 at 8:00 am
This was removed by the editor as SPAM
February 17, 2003 at 8:51 am
From Books On Line, you can find that the CONVERT function has a style argument. When converting from a money or smallmoney to a character datatype, pass in the value 1 as the style argument, like so:
SELECT CONVERT(CHAR(10), CONVERT(MONEY, FieldName), 1) AS "ColumnName" FROM TableName
Hope that helps,
Jay
(buried under 2 feet of snow here in sunny Columbus, OH)
Edited by - jpipes on 02/17/2003 08:56:36 AM
February 17, 2003 at 8:54 am
The General way that I use is a small user defined function that takes in an int, converts it to a string, before inserting commas in the appropriate places.
This scalar function can then be called in any proc where you want to format the output.
I am not aware of any native sql function that will do this.
February 17, 2003 at 8:56 am
My above comment refers specifically to integers rather than Money values
February 18, 2003 at 4:40 am
One of the key things asked for was no decimal places. The only other option I see off hand that may perform a hair better or about the same (since everything in the LEN statement is deterministic and should not rerun for that row just utilize the previous output). Is this
SELECT LTRIM(LEFT(CONVERT(CHAR(38),CONVERT(MONEY,FieldName), 1), 35)) AS "ColumnName"
If first converts to money, the to a fixed width char which money is right aligned in the output. Then the LEFT keeps all but the last three spots (removing decimal) and finally LTRIM removes all the leading spaces.
February 22, 2003 at 12:51 pm
OK...silly question...Why is the database engine worring about how to display a value? This is a function that belongs to the presentation side of the house. I can understand if this is an extract to an external system, Jay has the recommended solution for you but, I still would try to avoid storing or delivering formatted text to user applications. There is alot of overhead for the server in this approach.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply