Using CASE to change SELECT column values

Published: 20 Sep 2002

I want to select a different set of values in the QMF report screen for some set of values in the database. If...

Step 2 of 2:

You forgot to provide an Email Address.

This email address doesn’t appear to be valid.

This email address is already registered. Please login.

You have exceeded the maximum character limit.

Please provide a Corporate E-mail Address.
- I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.
Please check the box if you want to proceed.
- I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.
Please check the box if you want to proceed.

the value stored in the Column is "A" I want to show it as "B". How can I do this in a single SELECT query? Basically I want to put this logic in the QMF query:

If "select column_name" = "A"
   display "B"
If "select column_name" = "C"
   display "D"
etc..

You want a CASE structure, of which there are two varieties.

The searched-when-clause syntax is:

select
   case
     when column_name = 'A' then 'B'
     when column_name = 'C' then 'D'
     else null
   end as column_name
 from yourtable

The simple-when-clause syntax is:

select 
   case column_name
     when 'A' then 'B'
     when 'C' then 'D'
     else null
   end as column_name
 from yourtable

The ELSE clause is optional, and defaults to NULL, but it's a good idea to code ELSE NULL anyway. You could also assign some other expression in the ELSE clause, such as column_name, i.e. specify the same column, to leave it unchanged if it doesn't meet any of the conditions. Finally, I usually assign a column alias that is the same as the original column name, but you are free to call it anything you like, e.g. changed_column.

For More Information

Dozens more answers to tough SQL questions from Rudy Limeback.
The Best SQL Web Links: tips, tutorials, scripts, and more.
Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.