SQL Number Formatting

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

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)