SQL Number Formating

Question

I'm looking to use SQL to format a number with commas in the thousands, but no decimal (so can't use Money) - any suggestions?

I'm using SQL Server 2005, but feel free to answer for others as well (like MySQL)

Answer 1

In MySQL, the FORMAT() function will do the trick.

Answer 2

With TSQL you could cast to money and convert it will add the .00, but you could use replace or substring to remove.

replace(convert(varchar, cast(column as money), 1), '.00', '')

In SQL 2005 you could use a CLR function as well

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString FormatNumber(SqlInt32 number)
{
 return number.Value.ToString("N0");
}

and call it as any other user-defined function

SELECT dbo.FormatNumber(value)

Answer 3

In Oracle you can specify a format parameter to the to_char function:
TO_CHAR(1234, '9,999') --> 1,234

Answer 4

For SQL Server, you could format the number as money and then delete the right-most three characters.

replace(convert (varchar, convert (money, 109999), 1), '.00','')

Ugly, Isn't it.

Edit: Oh, too slow again.

Answer 5

Any specific reason you want this done on the server side? Seems like it is a task better suited for the client/report.

Otherwise, you are storing a number as a string just so you can keep the formatting how you want it -- but you just lost the ability to do even basic arithmetic on it without having to reconvert it to a number.

If you're really determined to do it in SQL and have a justifiable reason for it, I guess my vote is on Scott's method: Value --> Money --> Varchar --> Trim off the decimal portion

-- Kevin Fairchild