In this SQL Server tutorial, you will learn about SQL Server format number with commas without decimal places.
You will use the FORMAT() function to format the number with commas and without decimal places. Then use the LEFT() and LEN() functions together for the same purpose.
SQL Server Format Number with Commas without Decimal Places
As you know presenting the data in a meaningful way is very important because it enhances readability and helps us in comprehension of the data in a better way. So here you will format the number with commas but without decimal values.
Sometimes when you present the numeric values with decimal places, it creates a visual noise, so presenting only the whole number and removing the decimal part becomes necessary in that case.
Let’s what are methods available in SQL Server that we can use to format the number with commas without decimal places.
SQL Server Format Number with Commas without Decimal Places Using Format() Function
The FORMAT() function in SQL Server allows you to format the value based on the specified format strings.
For example, you have a Product_Sales table with columns SaleID, ProductName, and SalePrice which is shown below.
Now think that you need to format the SalePrice column value with commas but without the decimal values that you see in the above table. For that use the below query.
SELECT SaleID, ProductName, FORMAT(SalePrice,'###,###,###') As FormattedWithComma FROM Product_Sales;
After the execution of the above query, it formats the number of the SalePrice column with commas which is also known as a thousand separator.
For example, the Xbox Series X product sale price is 60,000 and this price or number is formatted with commas without decimal places.
Here within the FORMAT() function, the ‘###,###,###’ custom format specifier means separating the group of three digits with commas. The ‘#’ symbol is a placeholder for the digit from zero to nine.
SQL Server Format Number with Commas without Decimal Places Using Left() and Len() Function
For example, suppose you have a Sales table which is shown below.
Now look at the column SalesPrice which contains the sale price of each product as the LET TV sale price is 799.1234, but you want only the whole number part and each number contains the 4 decimal places with on dot(.) symbol.
To format the SalesPrice column with comma and without decimal places use the below query.
SELECT ProductName, SalesPrice, LEFT(FORMAT(SalesPrice, 'N'), LEN(SalesPrice)-5) AS FormattedSalesPrice FROM Sales;
In the above output or picture, the values of the SalesPrice column are formatted with commas and no decimals values. But here you need to notice while formatting that the above method can also reduce the character from the value.
In the query part LEFT(FORMAT(SalesPrice, ‘N’), LEN(SalesPrice)-5), the FORMAT(SalesPrice, ‘N’) function formats the SalesPrice column as a number with commas.
Then LEFT(FORMAT(SalesPrice, ‘N’), LEN(SalesPrice)-5) function extracted the leftmost 5 characters from the formatted value with commas.
- Remember, the above method is suitable for the fixed length of the digits before the decimal points. The value with varying digits may yield wrong results.
This is how to use the LEFT() and LEN() functions in combination to format the number with commas and without decimal places.
In this SQL Server tutorial, you learned how to format the number with commas without decimal places using the FORMAT() function in SQL Server. Also used the LEFT() and LEN() functions together to format the number with commas and without decimal places.
You may like to read:
- How to Format Numbers with Leading Zeros in SQL Server?
- Format Number with Thousands Separator in SQL Server
- How to Format Number with Commas and Decimal in SQL Server?
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.