Decoding function in oracle database

Question

Can someone explain the following SQL statement from Oracle DB:

select decode(TRIM(to_char(SYSDATE,'Day')),'Monday','3','1') from dual 

Answer 1

First of all, start with to_char . to_char(SYSDATE,'Day') will give you the day of the week that it is today. to_char allows to_char to convert a date (in this case, today's date since you specified sysdate ) to a string of a specific format. Take a look here at some other examples of date formats you can use:

http://www.techonthenet.com/oracle/functions/to_char.php

trim removes leading and trailing spaces.

Now for decode . You can think of decode as an if else statement. Take a look at:

http://www.techonthenet.com/oracle/functions/decode.php

In your specific example, you can read this statement as: if today Monday returns 3 else return 1.

decode also allows you to do something more complex:

 select decode(TRIM(to_char(SYSDATE,'Day')),'Monday','3','Tuesday',5,'1') from dual 

This will read: if today Monday returns 3, otherwise if today Tuesday returns 5, else return 1

Answer 2

This will return 3 if it is currently on Monday (and the locale is such that the day displays as “Monday”) and 1 otherwise.

DECODE(a, b,c, d,e, f,g, ..., h) will compare a with b , d , f , etc., in turn. If a - b , then DECODE returns c ; if a - d , then DECODE returns e ; and so on. If a not one of them, then DECODE returns h . ( h is optional; the default return value, if h not specified, is NULL .)

Answer 3

The following is an explanation in parts:

Sysdate

returns server date as 15-APR-19 and local based format

to_char (SYSDATE, 'Day')

converts to a string and returns the day of the week, for example, Monday for the date April 15 19

TRIM (to_char (SYSDATE, 'Day'))

removes empty spaces before and after

The decoding function is similar to if else statements and therefore simplifies the code. For the current example, if we write in SQL, then it should be like this:

 CASE WHEN TRIM(to_char(SYSDATE,'Day')) = 'Monday' THEN '3' WHEN TRIM(to_char(SYSDATE,'Day')) = 'Tuesday' THEN '4' ELSE '1' END 

For a complete reference, see https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm.