SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Basic FORMATting– #SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I saw the addition of FORMAT() to the T-SQL language, but didn’t play with it much. Recently it appeared in some code, and decided to experiment a bit. I had assumed this was mainly for dates, but it’s a general format/culture function that handles numbers as well.

On the doc’s page, there are the basic description of the parameters, which are NVARCHAR(), so passing in VARCHAR() causes an implicit conversion. It shouldn’t be much, but there are already performance penalties (see Aaron Bertrand’s piece), so don’t add to the overhead.

One good thing to note is that if you pass in invalid formats or cultures, a NULL is returned. Since the format and culture strings aren’t completely intuitive, this might be a source of issues in your code.

This is a neat function, relying on CLR formatting rules. That means I can do fun things like:

DECLARE @i int = 5000;

SELECT FORMAT(@i, N'USD$#');

Which returns:

USD$5000

Or even:

DECLARE @i INT = 5000
;
SELECT  FORMAT(@i, N'# dahlahs')
;
GO

Which gives me:

5000 dahlahs

There are lots of formats, and certainly lots of nuances to numeric formatting strings. It’s worth reading up if you plan to use this, but again, beware of performance. I’d avoid using this if the data size is large, maybe more than a few hundred rows.

After all, the database server is a shared resource, and using this CPU to handle simple formatting may not be the best use of your system.


Filed under: Blog Tagged: SQLNewBlogger, syndicated, T-SQL

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...