tags:

views:

57

answers:

3

How do I write a CASE statement within my SELECT to do the following:

I have a column called Values. This column can have the value b, c, or a. If it has the value b, I want the SELECT to return big; if c return small, and if a return large

+7  A: 
Case [Values] 
When 'a' Then 'large'
When 'b' Then 'big'
When 'c' Then 'small'
End
Thomas
+3  A: 
select 
    case values
        when 'a' then 'large'
        when 'b' then 'big'
        when 'c' then 'small'
    end as values_decoded
from table
Neil Barnwell
A: 

Another approach that can give you similar performance is this, which takes advantage of comparing single character strings:

SELECT 
SUBSTRING('large', 1, DATALENGTH('large')*(1-abs(sign(ASCII([Values]) - ASCII('a'))))) +
SUBSTRING('big', 1, DATALENGTH('big')*(1-abs(sign(ASCII([Values]) - ASCII('b'))))) +
SUBSTRING('small', 1, DATALENGTH('small')*(1-abs(sign(ASCII([Values]) - ASCII('c')))))  
FROM table
Dan Nesmith