MonkeyClicker

I have used the decode function in Oracle a lot. I am trying to find a similar function in MSSQL.

What is the function in SQL that works like DECODE in Oracle?

Using CASE Expressions

by Craig S. Mullins

CASE expressions are often over-looked but can be extremely useful to change very complex query requirements into simpler, and sometimes more efficient SQL statements. The CASE expression enables many forms of conditional processing to be placed into a SQL statement. By using CASE, more logic can be placed into SQL statements instead of being expressed in a host language or 4GL program.

Microsoft�s implementation of CASE expressions in SQL Server is compliant with the ANSI SQL-92 standard.

A Quick Example

Sometimes a quick example is worth several hundred descriptive words, so let�s take a look at a sample SQL statement using a CASE expression on the title table in the sample pubs database:
SQL Statement #1

SELECT title, price,
Budget = CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END,
FROM titles

This statement would return results similar to these:

Title Price Budget
---------------------- ----------- ---------------
Cooking with Computers 11.95 Moderate
Straight Talk About Co 19.99 Moderate
The Busy Executive's D 19.99 Moderate
You Can Combat Compute 2.99 Inexpensive
Silicon Valley Gastron 19.99 Moderate
The Gourmet Microwave 2.99 Inexpensive
But Is It User Friendl 22.95 Expensive
Secrets of Silicon Val 20.00 Moderate
Net Etiquette (null) Unknown

This output is not comprehensive but shows enough detail to help describe the effects of the CASE expression. The CASE expression is exercised on each row returned. It categorizes the contents of the price column into four different groups: expensive, moderate, inexpensive, and unknown. This is easier and more efficient than writing a SQL statement that uses UNION to combine the results of the four categories. The following UNION statement would return