SQL query: create category column based on a varchar column in table containing specific values

Question

I have a table similar to the following:

Date        Description     Value1  Value2  
01/01/2012  shiny colour    2       0       
01/01/2012  yellow colour   2       2       
03/01/2012  matt colour     2       2       
03/01/2012  matt            4       1   
03/01/2012  shiny           2       2   

I want to write a SELECT SQL query (T-SQL) that will output all of the above columns but also display an extra column as the output of the SELECT statement whose value depends on the presence of the word "colour" in the Description (if "colour" is present it would be one value, if not it would show a different value).

(I would also want to display another extra column on top of that whose value depends on the presence of the words "matt" or "shiny" in the Description column. But I assume the method of doing this would be similar).

I believe I should be able to do this using the COALESCE function but I'm not familiar with this and am struggling to get anything working?

EXTENSION

Hey, thanks for your answers. They're really helpful. I have one more extension to the question. My second generated column relies on info in the first generated column. So something like:

SELECT *,
CASE 
    WHEN Description LIKE '%colour%' THEN 'SomeValue'
    ELSE 'Unclassified'
END AS Category1,
CASE
    WHEN AnotherColumn LIKE 'Something' THEN 'SomeValue'
    WHEN Category1='Unclassified' THEN 'Unclassified'
    ELSE 'Generic'
END AS Category2
FROM table_name

How do I get the output of Category2 to rely on output of Category1? I'm trying something like the above but it's not working.

My extension question was answered here: T-SQL CASE statement relies on another CASE statement in same SELECT query

Answer 1

SELECT *,
       CASE WHEN Description LIKE '%colour%' THEN
            1
       ELSE
            0
       END AS HasColour,
       CASE WHEN Description LIKE '%matt%' THEN
            1
       ELSE
            0
       END AS HasMatt,
       CASE WHEN Description LIKE '%shiny%' THEN
            1
       ELSE
            0
       END AS HasShiny
FROM   table_name

You would just add more columns for all the different words that you want to search for. Obviously you can change the return type of the columns to whatever you want, but I thought a boolean would be suitable in this situation.