Tutorial :SQL Case Statment


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


Case [Values]   When 'a' Then 'large'  When 'b' Then 'big'  When 'c' Then 'small'  End  


select       case values          when 'a' then 'large'          when 'b' then 'big'          when 'c' then 'small'      end as values_decoded  from table  


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  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »