Format Decimal Datatype with Commas in T-SQL

Note: This is approach is actually NOT recommended. Better idea would be to do all the formatting on UI.

Ever fall into a situation where a column is a Decimal, and you want the output to have a comma after every three digits?
T-SQL’s CONVERT function can do it for you. The syntax of the function is:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Here, we will be utilizing the power of the last parameter style. The style can be set to the following three values, when converting money datatype into a character datatype:

0 – No commas and only 2 decimal points
1 – Commas every 3 digits, and only 2 decimal points
2 – No command, and 4 decimal points.

Now since, the datatype stored in the table is actually decimal, we first need to convert it into money, and then convert it into varchar or char.

select convert(varchar(50),convert(money,'54120000.2154'),1)

The result would be:

-------------------- 54,120,000.22 (1 row(s) affected) Simple. Isn’t it?

Why NULL equals NULL is False

NULL has a special meaning. This was created to fulfill the need of having a character, that means ’nothing’ and it equals ’nothing’. So, it doesn’t even equal itself. Suppose, you have a column named ’PRICE’ in your table with datatype ‘money’. For a particular product, you don’t have data for price. How would you store it? Store a zero? But that would mean that the product is free!

Consider another scenario. You have a table, where you store the comments written by visitors on your site. Whenever a comment is written, you have to either approve it or disapprove it. If you disapprove, a ‘sorry’ mail is sent, if you approve, that comment is shown on your site, and a ‘thank you’ mail is sent. In this case, what would be the value of the column ‘approved’ initially?

Above two cases make a good case for storing a NULL.

Now the problem, how would you search for NULL in your query? The obvious answer would be to use something like:

select * from sometable where somecolumn=NULL

But above will NOT work. The reason is NULL is a special character and it equals nothing, not even it self. So, the solution to this problem is the IS NULL operator:

select * from sometable where somecolumn IS NULL

This works perfectly. Another solution is to set ANSI_NULLS to off:

This will work fine as well. The reason for this is compliance to SQL-92 standards. When you set ANSI_NULLS to off, you are actually turning off the compliance to SQL-92 standards. Read more about it here.

Happy Coding!