[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)
Enquirer: Sunil

||
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.

Respondent: Mitch Wheat
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.

Respondent: Mahmoud Gamal
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.

Respondent: Govind
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)
Respondent: Sunil
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 .

Leave a Reply

Your email address will not be published.