Export (0) Print
Expand All

Using CASE

SQL Server 2000

Using CASE

The CASE function is a special Transact-SQL expression that allows an alternative value to be displayed depending on the value of a column. This change in data is temporary; therefore, there are no permanent changes to the data. For example, the CASE function can display California in a query result set for rows that have the value CA in the state column.

The CASE function consists of:

  • The CASE keyword.

  • The column name to transform.

  • WHEN clauses specifying the expressions to search for and THEN clauses specifying the expressions to replace them with.

  • The END keyword.

  • An optional AS clause defining an alias for the CASE function.

This example displays, in the query result set, the full name of the state each author lives in:

SELECT au_fname, au_lname, 
   CASE state
      WHEN 'CA' THEN 'California'
      WHEN 'KS' THEN 'Kansas'
      WHEN 'TN' THEN 'Tennessee'
      WHEN 'OR' THEN 'Oregon'
      WHEN 'MI' THEN 'Michigan'
      WHEN 'IN' THEN 'Indiana'
      WHEN 'MD' THEN 'Maryland'
      WHEN 'UT' THEN 'Utah'
        END AS StateName
FROM pubs.dbo.authors
ORDER BY au_lname

See Also

CASE

NULLIF

COALESCE

Conditional Data Processing Using CASE

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft