Format numbers in SQL Server


By:   |   Updated: 2021-11-01   |   Comments   |   Related: More > 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:

Interested in MSSQL String functions?  Check out these articles:

Interested in SQL Server date functions?  Check out these articles:






get scripts

next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips


Article Last Updated: 2021-11-01

Comments For This Article





download














get free sql tips
agree to terms