Part 4: Miscellaneous Functions
This last category of important SQL Server functions includes those that deal with nulls, conversion, and control flow. Far from leftovers, these functions are among some of the most useful you'll ever come across!
COALESCE
Anytime you select a column whose value is not mandatory, you're bound to encounter null values. That only makes sense, because null values represent absent or missing information. Trouble is, nulls can reek havoc when included in calculations as well as other operations that one might perform on column data.
The COALESCE function accepts a list of arguments and returns the first one that does not contain a null value. Hence, SQL Server proceeds through each input parameter you provided until it either encounters one that isn't null or simply runs out of arguments. Here's its syntax:
COALESCE(val1, val2, ...., val_n)
It is commonplace to substitute a value of zero in the place of null. In some instances a different value may make more sense. for example, the film table of the Sakila Sample Database contains a column named original_language_id for films that are not originally in English. We can employ COALESCE to set its value to 1 (the language_id for English) whenever a null is found:
CONVERT
Converting an output value into a specified data type is par for the course in database work. In SQL Server, you can change the data type of a value to another using the CONVERT function. Its syntax is simple:
CONVERT(type, value)
One good reason to use CONVERT is for removing the time portion from a datetime field. Here's a query that shows the same field in its original datetime format and without the time portion:
IIF
If/else statements are the most commonly used control flow structures in programming. SQL Server provides the power of the if/else statement to our queries in the form of the IIF function. Its syntax is:
IIF(expression, value_if_true, value_if_false)
We can utilize the IIF function to separate film lengths into three groups - Short, Medium, and Long - depending on their lengths. We'll categorize film lengths as follows:
- Short: under 80 minutes
- Medium between 80 and 120 minutes
- Long: over 120 minutes
We use the IIF() function to compare the length of the film to a given expression, and depending on the result, it returns a 1 or a NULL. If it returns a 1, it will be counted under that column heading (Short, Medium, or Long):
Conclusion
That brings us to the end of our series on the most important SQL Server Functions. As mentioned at the beginning of the series, it's helpful to know the exact function names and signatures for your specific database type because they can vary from provider to provider. Case in point, the IIF function is only found in Microsoft products.
Interested in Navicat for SQL Server? You can try it for 14 days completely free of charge for evaluation purposes!
Rob Gravelle resides in Ottawa, Canada, and has been an IT Guru for over 20 years. In that time, Rob has built systems for intelligence-related organizations such as Canada Border Services and various commercial businesses. In his spare time, Rob has become an accomplished music artist with several CDs and digital releases to his credit.
