Number formatting in a SQL select statement

Hi

I'm trying to convert and format integer values in a SQL Server select statement to a string
representation of the number formated with ,'s (1000000 becomes 1,000,000 for example).

I've been looking at CAST and CONVERT and think the answers there somewhere. I just don't
seem to be able to work it out.

Anyone out there able to help me please?

Thanks,
Keith.

My suggestion would be to do this on the front end rather than the back end.
In any case, I think you will need to CAST your column as a money data type, and then CONVERT it using style 1, like this:
SELECT
CONVERT(varchar(20),CAST(myColumn AS money) ,1)
This will unfortuately also return the 2 digits after the decimal point. So my next step would be to strip them out.
This seems very messy, though. Hopefully someone else will have a better idea.

|||Yeah, that is messy.
<soapbox>
The first question I have is why isn't this being done in yourpresentation layer? SQL's strong suit is selecting data, not formattingit. Your ASP.NET environment already has tools that make this mucheasier than anything that we can come up with in SQL.
</soapbox>
Even messier would be this code sample, which is a function and/orstored procedure that accomplish what you are looking for. I've neverused it, but it looks right to me.
Jason
Update: Forgot to link - http://www.issociate.de/board/post/176502/How_do_I_format_an_integer.html
|||

Definitely a front-end issue. I've had to use SQL to format results when using SQLMail and it's a nightmare. Possible using combinations of cast, convert, charindex, substring, etc., but a nightmare. Use the front-end.

|||

Try this url it is using Strings and Formatting in the Framework Class library to do custom formatting. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstringsoutputexample.asp

Kind regards,

Gift Peddie