I came across another obscure issue in T-SQL last week. While throwing together a quick query, I came across a scenario where I needed to return a number formatted with commas and no decimal places. I know most would say that logically you'd handle formatting in the calling application, but in this case it really needed to be handled in the query itself (trust me). Here's an example of what I wanted returned...
example: 65,236,541
Seemed simple enough, right?
I was surprised to find that there was no direct way to accomplish this using any built-in T-SQL functions. You actually have to assemble a nested set of formatting functions each time you needed to output the number. Rssentially, you have to convert the number to a money type which allows you to format it with commas and 2 decimal places, and then strip the decimal point and the two trailing zeros. Although it works fine, it doesn't make for very readable code. So I wrote this simple function to do it instead...
/*=========================================================
Procedure: fn_FormatInteger
Input: @nNumber - The number to format
Returns: varchar
Description: rounds and converts a number into a
varchar and formats it with comma separated
thousands and no decimal point.
example: 789654123.3698741 --> 789,654,123
=========================================================*/
ALTER Function dbo.fn_FormatInteger (
@nNumber numeric
)
RETURNS varchar(100)
AS
BEGIN
DECLARE @strOut varchar(100)
SET @strOut =
SUBSTRING(
CONVERT(
varchar,
CAST(@nNumber AS money),
1
),
1,
LEN(
CONVERT(
varchar,
CAST(@nNumber AS money),
1
)
) - 3
)
RETURN(@strOut)
END
----------------------
Note: Tested only with SQL Server 2000
I realize I'm not bowling over any code-heads out there with this one, but since I didn't find anything like this out there while researching a more direct solution, I thought I'd pass it along.
'Hope it helps.
tags: sql, t-sql, sql server