Tutorial :SQL Case Statment



Question:

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


Solution:1

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


Solution:2

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


Solution:3

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
Previous
Next Post »