By: Daniel Calbimonte | Updated: 2021-11-01 | Comments (2) | Related: 1 | 2 | 3 | 4 | 5 | > Functions System
Problem
Sometimes we have numbers in a different format due to cultural differences. For example, in France we use a comma for decimals and in USA we use decimal points. How can we work and change the format of numbers when working with Microsoft SQL Server?
Solution
We will provide practical examples about different scenarios using different solutions for ways to format numbers in SQL Server using various SQL functions.
SQL Format Number Options
In this tutorial, we will cover how to use the following SQL Server T-SQL functions with the following examples:
- Using CAST - SELECT CAST(5634.6334 as int) as number
- Using CONVERT - SELECT CONVERT( int, 5634.6334) as number
- Using ROUND - SELECT ROUND(5634.6334,2) as number
- Using CEILING - SELECT FLOOR(5634.6334) as number
- Using FLOOR - SELECT CEILING(5634.6334) as number
- Using FORMAT - SELECT FORMAT(5634.6334, 'N', 'en-us') AS 'Number'
SQL Format Number using CAST function
Let’s say that we have the following number: 5634.6343
We can use the SQL CAST function to change the format of the number as follows:
| Code | Result |
|---|---|
| SELECT CAST(5634.6334 as int) as number | 5634 |
| SELECT CAST(5634.6334 as numeric) as number | 5635 |
| SELECT CAST(5634.6334 as numeric(10,1)) as number | 5634.6 |
| SELECT CAST(5634.6334 as numeric(10,2)) as number | 5634.63 |
SQL Format Number using CONVERT
The SQL CONVERT function can do the same things as CAST. It has different syntax and in some scenarios, it has additional options. The following table shows some examples like the ones used for CAST.
| Code | Result |
|---|---|
| SELECT CONVERT( int, 5634.6334) as number | 5634 |
| SELECT CONVERT( numeric, 5634.6334) as number | 5635 |
| SELECT CONVERT( numeric(10,1), 5634.6334) as number | 5634.6 |
| SELECT CONVERT( numeric(10,2), 5634.6334) as number | 5634.63 |
| SELECT (CONVERT( nvarchar(20), 5634.6334))+'€' as number | 5634.6334€ |
| SELECT REPLACE((CONVERT(nvarchar(20), 5634.6334)),'.',',') as number | 5634,6334 |
| SELECT CONVERT( nvarchar(20), 50) + '%' as number | 50% |
SQL Format Number using ROUND function
The SQL ROUND function may be useful if you want to round the number of decimal places. Here are some common examples:
| Code | Result |
|---|---|
| SELECT ROUND(5634.6334,2) as number | 5634.6300 |
| SELECT ROUND(5634.6334,3) as number | 5634.6330 |
| SELECT ROUND(5634.6334,-1) as number | 5630.0000 |
| SELECT ROUND(5634.6334,-2) as number | 5600.0000 |
SQL Format Number using FLOOR AND CEILING functions
The FLOOR function returns the largest integer less or equal to the number while the CEILING returns the smallest integer greater or equal to the number. Here are some examples:
| Code | Result |
|---|---|
| SELECT FLOOR(5634.6334) as number | 5634 |
| SELECT CEILING(5634.6334) as number | 5635 |
| SELECT FLOOR(-5634.6334) as number | -5635 |
| SELECT CEILING(-5634.6334) as number | -5634 |
SQL Number Format using FORMAT function
The SQL FORMAT option has many different options for formatting a number. Here are some useful examples:
| Format | Code | Result |
|---|---|---|
| Numeric Format | SELECT FORMAT(5634.6334, 'N', 'en-us') AS 'Number' | 5,634.63 |
| Numeric format – 1 decimal | SELECT FORMAT(5634.6334, 'N1', 'en-us') AS 'Number' | 5,634.6 |
| Exponential, Scientific notation | SELECT FORMAT(5634.6334, 'E', 'en-us') AS 'Number' | 5.634633E+003 |
| Exponential, Scientific notation, 2 decimals | SELECT FORMAT(5634.6334, 'E2', 'en-us') AS 'Number' | 5.63E+003 |
| Decimal | SELECT FORMAT(5634, 'D', 'en-us') AS 'Number' | 5634 |
| Decimal-6 digits | SELECT FORMAT(5634, 'D6', 'en-us') AS 'Currency Format' | 005634 |
| General Format | SELECT FORMAT(5634.6334, 'G', 'en-us') AS 'Number' | 5634.6334 |
| General format, 6 digits | SELECT FORMAT(5634.6334, 'G6', 'en-us') AS 'Number' | 5634.63 |
| Currency-England | SELECT FORMAT(200.36, 'C', 'en-GB') AS 'Number' | £5,634.63 |
| Currency-China | SELECT FORMAT(5634.6334, 'C', 'zh-CN') AS 'Number' | ¥5,634.63 |
| Percentage | SELECT FORMAT(0.5, 'P', 'en-us') AS 'number' | 50.00% |
| Percentage 4 decimals | SELECT FORMAT(0.5, 'P4', 'en-us') AS 'number' | 50.0000% |
| Hexadecimal | SELECT FORMAT(56344, 'X', 'en-us') AS 'number' | DC18 |
| Phone number | SELECT FORMAT(123456789,'+###-###-####') AS 'number' | +123-45-6789 |
| Fixed point | SELECT FORMAT(5634.6334, 'F', 'en-us') AS 'Number' | 5634.63 |
| Fixed point – 8 digits | SELECT FORMAT(5634.6334, 'F8', 'en-us') AS 'Number' | 5634.63340000 |
Conclusion - SQL Server Formatting Numbers
In this tutorial, we saw different examples of functions used to change the numeric formats.
Next Steps
For more information refer to the following links:
- SQL Server Rounding Functions - Round, Ceiling and Floor
- Learn how to convert data with SQL CAST and SQL CONVERT
Interested in MSSQL String functions? Check out these articles:
- SQL String functions in SQL Server, Oracle and PostgreSQL
- SQL Server 2016 STRING_SPLIT Function
- New FORMAT and CONCAT Functions in SQL Server 2012
- SQL Server SUBSTRING
- SQL Server Substring Function Example with T-SQL, R and Python
Interested in SQL Server date functions? Check out these articles:
- SQL Convert Date to YYYY-MM-DD
- How SQL Server handles the date format YYYY-MM-DD
- Format SQL Server Dates with FORMAT Function
- Date and Time Conversions Using SQL Server with CONVERT
- How to Get Current Date in SQL Server
- SQL Server DateTime Best Practices
About the author
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2021-11-01
