Format a number with commas but no decimals in SQL Server 2008 R2?

Question

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

SELECT CONVERT(varchar, CAST(1112 AS money), 1) 

Answer 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 suitable for execution in an interface.

Answer 2

Same:

 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) always returns a number with .00 . However, MitchWheat's answer is better if there is a decimal number after the decimal point.

Please note that . You should think about this using this formatting material in a foreground application. T-SQL does not apply to formatting.

Answer 3

 PARSENAME(CONVERT(VARCHAR,CAST(1112 AS MONEY),1),2) 

This will work well.

When automatically converting a number to a monetary data type, 2 zeros will be added after the decimal place. The PARSENAME function will remove these zeros.

Answer 4

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

Option 1 Answer from Mitch Wheat is a possible answer. However, when we need to format the column value in SELECT, we would need to create a user-defined scalar function using the Mitch 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 a built-in system function called "parseame", as in the T-SQL code below, to format and truncate decimal places.

 SELECT PARSENAME(CONVERT(VARCHAR, CAST('2334442221.345222' AS MONEY), 1),2) 

Answer 5

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

Starting with SQL 2012 you can write:

 SELECT FORMAT(ColumnName, 'N0');