SQL Server: basic functions (part 7) - IsNull, IsNumeric, IsDate

Tweet

This is the seventh part of the Sql Server basic functions series. When I started it, I couldn't think of going that far. Honestly, I thought I would write about some functions, however, after a while, I started to understand that leaving other functions out would be unfair. That's why I would like to write about IsNull, IsNumeric and IsDate... and maybe some other functions in the near future.

IsNull

I've already mentioned the IsNull function in the Coalesce post - as you may remember. That is because IsNull may be confused with Coalesce. You could use Coalesce instead of IsNull however I don't think that would be efficient.
Just to understand the difference we can write the two functions to obtain the same result (Hello!) as:

SELECT COALESCE (Null, Null, 'Hello!') FROM your_table

and

SELECT ISNULL(Null, ISNULL(Null, 'Hello!')) from your_table

With the above example, it now may be clear to you the power of Coalesce compared to IsNull. Nevertheless, sometimes IsNull can be used in a easier way for easier tasks.
So, remember that IsNull replaces null values with a specified value. That is to say that:

SELECT ISNULL(Price, 0) as price FROM your_table

will replace all null prices with zero. Simple as that. You may understand that avoiding null values in a query resultset is very important, so anytime you might end up dealing with potential null values, use IsNull.

IsNull vs IS [NOT] NULL
Do not confuse the IsNull function with IS [NOT] NULL that it is used to determine if a value is null or is not null. Specifically you use it in WHERE clauses like:

SELECT Price FROM your_table WHERE Price IS NOT NULL

The above query will leave the null prices out of your resultset.

IsNumeric

IsNumeric determines if a value is a valid numeric type. Before manipulating numeric value, it may be handy to determine if that numeric value is indeed numeric. Wow! That's strange! We always try to deal with numeric value without knowing if they are numeric! - sarcastic!
Do not underestimate the function. Believe me it might be very useful.

SELECT ISNUMERIC(Price) as num_check FROM your_table

The above query will return a list of 1, given that the Price column contains only numeric values. While

SELECT ISNUMERIC(Name) as num_check FROM your_table

will return a list of 0, because the Name column contains only chars.

IsDate

As IsNumeric, IsDate will determine if a value is really a date or not. Again

SELECT ISDATE(Production_Date) as date_check FROM your_table

will return 1, wherever Production_Date is a really a date and 0 if it is not a date.

That's all for now. See you in the next part of the series.