Codeedoc - Programming Blog

Oracle DECODE And T-SQL CASE In Microsoft Sql Server

Decode function in Oracle:

decode (expression, search_1, result_1, search_2, result_2, …., search_n, result_n);
decode (expression, search_1, result_1, search_2, result_2, …., search_n, result_n, default);

For example:

create table example (x int, y int);

insert into example values (1,2);
insert into example values (1,3);
insert into example values (2,4);
insert into example values (2,5);
insert into example values (3,6);

Following query:

select * from example where y = decode (x,1,3,3,6);

Results In:

X Y
———————
1 3
3 6

Decode function can be simulated in Microsoft Sql Server using the T-Sql Case expression. The same query in t-sql would look like this:

SELECT X,
CASE X WHEN 1 THEN 3 WHEN 3 THEN 6 ELSE NULL END AS ‘Y’
FROM example