# [Solved] Format a number with commas but without decimals in SQL Server 2008 R2?

I am using the following to create a comma formatted number in T-SQL. How can I get rid of the decimal point and the digits after decimal. So if I get 1,112.00 after formatting, how would I get only 1,112?

``````SELECT CONVERT(varchar, CAST(1112 AS money), 1)
``````
Solution #1:
``````DECLARE @val varchar(50)

set @val = CONVERT(varchar(50), CAST(1112 AS money), 1)
SELECT  left(@val, len(@val) - 3)
``````

This also works with digits after the decimal point:

``````DECLARE @val varchar(50)

set @val = CONVERT(varchar(50), CAST(1112.56 AS money), 1)
SELECT  left(@val, len(@val) - 3)
``````

Note: as @Mahmoud Gamal points out, formatting is often more suited to be performed in the front-end.

Solution #2:

Like so:

``````SELECT REPLACE(CONVERT(VARCHAR(20), CAST(1112 AS MONEY), 1), '.00', '');
``````

This will always work fine. Since `CONVERT(VARCHAR(20), CAST(1112 AS MONEY), 1)` will always return a number with `.00`. However, the MitchWheat’s answer is better in case there is a number with decimal numbers after the comma.

Note that: You should consider to do this formatting stuff in the front end application. T-SQL is not about formatting.

Solution #3:
``````PARSENAME(CONVERT(VARCHAR,CAST(1112 AS MONEY),1),2)
``````

It will work nicely.

When convert number to money datatype automatically 2 zeros will be added after decimal.

PARSENAME function will remove that zeros.

Solution #4:

https://database.guide/how-to-format-numbers-in-sql-server/

Starting on SQL 2012 you can write:

``````SELECT FORMAT(ColumnName, 'N0');
``````
Solution #5:

After some research, I found 2 possible answers to my initial question. They are listed below.

Option 1: The answer from Mitch Wheat is a possible answer. However, when one wants to format a column value within a SELECT, then we would have to create a user-defined scalar function using Mitch’s T-SQL code, and call this UDF from our SQL.

``````-- =============================================
-- Description: Formats a number and truncates
--              the decimal part. You can pass
--              a number as a string or a numeric type.
-- =============================================
CREATE FUNCTION dbo.Utility_fn_FormatNumberAndTruncateDecimals
(
@unFormattedNumber VARCHAR(100)
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @val VARCHAR(100)
SET @val = convert(VARCHAR(50), cast(@unFormattedNumber AS MONEY), 1)

RETURN (SELECT left(@val, len(@val) - 3))
END
GO

--call this function using either of the SELECTS below
SELECT dbo.Utility_fn_FormatNumberAndTruncateDecimals('233444')
SELECT dbo.Utility_fn_FormatNumberAndTruncateDecimals(233444.345)
``````

Option 2: We can use an inbuilt system function called ‘parsename’ as in T-SQL code below, to format and truncate decimals.

``````SELECT PARSENAME(CONVERT(VARCHAR, CAST('2334442221.345222'  AS MONEY), 1),2)
``````
The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .