Using DECODE Function Of ORACLE In SQL Server
This article explains how to use DECODE Function Of ORACLE In SQL Server.
In ORACLE, Decode function is used to implement if-then-else type of requirement. DECODE Function is not available in SQL Server. In ORACLE, the syntax for DECODE function is:
SELECT DECODE("ColumnName","Search_Data_1","result1",...."SearchData_n","result_n" )
where Search_Data is the value to be searched and result is that value that is displayed in place of Search_Data.
Example:
Creation of Table
CREATE TABLE BOOK
(
[Id] int IDENTITY(1,1) NOT NULL,
[Name] nvarchar(50) NULL,
[Author] nvarchar(50) NULL,
[Price] decimal(18, 2) NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)
Insert Data and select table values
insert into BOOK
select 'ASP.NET 4.0 UNLEASHED','Stephen Walther',600 union all
select 'DATA STRUCTURES','SEYMOUR LIPSCHUTZ',300 union all
select 'jQuery UI','Eric Sarrion',200
select * from BOOK
Output:
In SQL Server DECODE function is implemented as:
select case when Author='Stephen Walther' then 1
when Author='SEYMOUR LIPSCHUTZ' then 2
when Author='Eric Sarrion' then 3
else 0 end as Author
from BOOK
Output: