SQL Server Format Number with Commas without Decimal Places

by

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.

SQL Server Format Number with Commas without Decimal Places Using Format() Function Table Product Sales

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;
SQL Server Format Number with Commas without Decimal Places Using Format() Function

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

The LEFT() function in SQL Server retrieves the leftmost characters from the strings and the LEN() function returns the number of characters of the given string value.

For example, suppose you have a Sales table which is shown below.

SQL Server Format Number with Commas without Decimal Places Using Left() and Len() Function Sales Table

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;
SQL Server Format Number with Commas without Decimal Places Using Left() and Len() Function

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.

Conclusion

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: