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.
IsNullI'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
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.
SELECT ISNULL(Null, ISNULL(Null, 'Hello!')) from your_table
So, remember that IsNull replaces null values with a specified value. That is to say that:
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.
SELECT ISNULL(Price, 0) as price FROM your_table
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:
The above query will leave the null prices out of your resultset.
SELECT Price FROM your_table WHERE Price IS NOT NULL
IsNumericIsNumeric 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.
The above query will return a list of 1, given that the Price column contains only numeric values. While
SELECT ISNUMERIC(Price) as num_check FROM your_table
will return a list of 0, because the Name column contains only chars.
SELECT ISNUMERIC(Name) as num_check FROM your_table
IsDateAs IsNumeric, IsDate will determine if a value is really a date or not. Again
will return 1, wherever Production_Date is a really a date and 0 if it is not a date.
SELECT ISDATE(Production_Date) as date_check FROM your_table
That's all for now. See you in the next part of the series.