Using DECODE Function Of ORACLE In SQL Server

This article explains how to use DECODE Function Of ORACLE In SQL Server.
  • 2545

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:

Book_Table.jpg

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:

DecodeFunction.jpg


Categories

More Articles

© 2013 dotNetheaven. All rights reserved.