SQL Server: basic functions (part 2) - The Case function

This is the second part of series of posts on basic SQL functions. In the first part we saw right, left, substring, rtrim, ltrim and len.
In this post we are going to analyse the CASE function.

The Case function

This is a very useful function which has numerous applications. It evaluates a series of conditions and returns one of multiple possible results, based on the conditions. You can see it as the If... Then... Else... End if in VBScript.
First of all, remember that the function can be a simple CASE or a searched CASE function.

The simple CASE function

SELECT ProductCode, Category =
   CASE Line
      WHEN 'V' THEN 'Vegetable'
      WHEN 'F' THEN 'Fruit'
       ELSE 'Not available'
    END
FROM ProductTable

As you can see the function evaluates the Line field: if it contains 'V', Category will be 'Vegetable'; if it contains 'F', Category will be 'Fruit', otherwise Category will be 'Not available'.
In this case, the function evaluates the content of a field in order to fill in another - new - field (which is not actually a column of the ProductTable table).

The searched CASE function

SELECT ProductCode, 'Price Range' =
   CASE
     WHEN Price < 100 THEN 'Cheap'
     WHEN Price = 100 THEN 'Average'
     WHEN Price > 100 and Price < 1000 THEN 'Expensive'
     ELSE 'Too Expensive'
   END
FROM ProductTable

In the example, the function evaluates the Price field in order to give a product the correct Price Range.

The CASE function as If.. Then..
You can use the CASE function as If... Then... Else. As an example, you want to evaluate a field like this:

If the Price field is null then the price is 'upon request', otherwise show the price.

Let's see it in action:

SELECT ProductCode, Price =
   CASE
      WHEN Price is null THEN 'Upon request'
      ELSE Price
   END
FROM ProductTable

Nested CASE function

You can use the function in a nested way:

SELECT ProductCode, Price =
   CASE
     WHEN Customer is null THEN 'Contact the sales department'
      ELSE
        CASE
           WHEN Price < 100 THEN Price
            ELSE 'Upon request'
        END
      END
FROM ProductTable

The above example is just to give an idea of how you could use the CASE function in a nested way. Usually you do not need it, but I thought you should know anyway.

Where to use the CASE function
The CASE function can be used wherever you want. You can use it in update, insert, delete or select statements. You can use it in where, having, and order by clauses.
It can be used with the ELSE argument (as seen in the examples) or without it.
In my opinion, due to its flexibility, the CASE function has multiple applications and simplify data manipulation a lot.

In the next post of the series, we are going to see cast and convert.

Have a nice day, and don't forget to comment.