Search
Monday, November 12, 2018 ..:: Home ::.. Register  Login

Peter Henry on Facebook  Peter Henry on LinkedIn  Peter Henry on Twitter

   Calendar  
     
  
   Search  
     
  
   Blogroll  
     
  
   Disclosure  
All blog entries are the opinions of the author and do not necessarily reflect the opinions of their employer. All the code presented is for explanation and demonstration purposes only. Any damages incurred to your site and/or data are not the responsibility of the author. Every effort is taken to ensure the code properly compiles, however sometimes there are some hiccups and you might be required to do your own debugging.
     
  
   TechTidBits (Blog)  

How do you format REALLY big numbers in a select to differentiate them?

Feb 24

Written by:
Monday, February 24, 2014 8:53 PM  RssIcon

TSqlFundamentalsYou have some REALLY big numbers in your T-SQL statement, how do tell the millions from the thousands?  I have a quick trick.

If you’re writing some T-SQL to check sums or maybe counts, it’s usually pretty important to write down the RIGHT amount, right?  I mean, if you’re going to miss the ‘2’ in the thousands column cause you just couldn’t see it (maybe not enough coffee?), it’s going to probably cost you a lot of time to redo that work and refind those values right?

Well, I think I have a quick trick.  The hiccup is I can never remember, so I’m writing this post so I have something to copy’n’paste from.

declare @reallyBigNumber int = 2282613
select CAST(CONVERT(varchar, CAST(@reallyBigNumber AS money), 1) AS varchar)

There’s TWO pieces of magic in the above sql.  First is the casting to money, then the conversion using the style of 1 with the CONVERT function.  The last cast I believe is meant to display it properly, I believe this could optionally be skipped.

If you don’t believe me, check out what happens when each small piece is broken out.

image

There is ONE caveat with this!!!!!!  Most senior devs will be chomping at the bit yelling at their screens like the Swedes today yelling into their screens for the 2014 Olympic men’s hockey final…..NNNNOOOOOO!!!!!!!!!  Remember, waaaaay up top, I said IF your writing sql to check stuff, or investigate or analyze!  I would REALLY suggest NOT doing this for production systems in any way!  If you’re looking for format your output, I think you’re doing it at the wrong place and SHOULD be looking at your client app instead of here on the db server.

Now that you know how to quickly differentiate your millions from your thousands, it’s time to grab a coffee and get coding!

 

Resources

MSDN: CAST and CONVERT (Transact-SQL)

Tags:
Categories:
Location: Blogs Parent Separator TechTidBits

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Add Comment   Cancel 
     
  
Copyright 1999-2012 by PCHenry.com   Terms Of Use  Privacy Statement