Friday, 10 December 2010

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


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.

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
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 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.

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.

0 thoughts:

Post a Comment

Comments are moderated. I apologize if I don't publish comments immediately.

However, I do answer to all the comments.