Making Numbers Look Better

The last time we were together, we discussed Making Better Numbers. Hopefully, you took a few minutes to review some of the examples I provided, figure out which ones do and do not make sense to you, and try to figure out some examples on your own. If not, try doing that before reading any further in today’s blog post!

All right – glad you’re back with us again! Now that you have figured out your favorite of CONVERT and CAST and how to work with different numbers, let’s do a bit more with numbers.

First, we want one of my favorite public data sets out there – Lahman’s Baseball Database

Just import that data into your SQL Server database and we can get started!

In the dbo.HomeGames table, there is a field called Attendance – meaning how many people attended each game. Let’s see how we would add a comma to the number for formatting purposes.

We could always do our friend – string manipulation – and a couple of conversions to make them all show with a comma.

SELECT REPLACE(CONVERT(VARCHAR(20),CONVERT(MONEY,attendance),1), '.00','')
FROM dbo.HomeGames

This actually does work as expected but it truly looks awful code-wise. For fun, let’s walk thru what all this is doing:

1. Convert the field to being seen as money. (Yes, this means that if your value is greater than 922,337,203,685,477 it will error out – since that is the maximum number that the money data type will allow.)
2. Convert that money to being a varchar(20) but with the additional parameter of 1 – which tells SQL Server to use a comma every 3 characters and 2 places to the right of the decimal.
3. Replace the “.00” with a blank string.

Three steps are a lot to remember if you want to make this work. Funnily enough, this is one of the better solutions that would work prior to SQL Server 2012.

In SQL Server 2012 and any version after that, however, you can run the following command:

SELECT FORMAT(attendance, 'N0')
FROM dbo.HomeGames

Much easier to remember.

1. One real function – FORMAT – that tells SQL Server to format the data.
2. One parameter – ‘N0’ – to tell it the number of decimal places to include.

The extra benefit of using the FORMAT function is if you’re in a country that doesn’t use the commas as a separator, you’re good to go right away!

If the format that you want to see the data in isn’t setup as your standard format on your computer, you can do as we did with dates and tell SQL Server the culture that you’d like to see it in as shown below:

SELECT FORMAT(attendance, 'N0', 'en-us'),
	FORMAT(attendance, 'N0', 'ru-ru'),
	FORMAT(attendance, 'N0', 'hi-IN'),
	FORMAT(attendance, 'N0', 'de-DE'),
	FORMAT(attendance, 'N0', 'en-gb')
FROM dbo.HomeGames

With this, you will see all kinds of different formats for the attendance numbers of the baseball games.

Now, if you know that you’re going to always show your data in the US format – then you can do something else!

SELECT FORMAT(Attendance, '#,###')
FROM dbo.HomeGames

Yes, that’s right my friends! You can tell SQL Server yet another way of formatting numbers as you’d like to view them!

Hopefully, by now you’ve seen that SQL Server gives you a variety of ways of making your numeric data look nice and presentable to your users.

And with that, my friends, I hope you have learned something new about showing numeric data off to users and maybe, just maybe, you’ll be able to show it off on your next report! Until next time!