Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

TO_BINARY_FLOAT Function in Oracle SQL – PLSQL

by

The TO_BINARY_FLOAT function in Oracle SQL / PLSQL is used to convert a numeric value into TO_BINARY_FLOAT.

Syntax for the TO_BINARY_FLOAT function in Oracle SQL / PLSQL is:

SELECT TO_BINARY_FLOAT(number)
FROM table_name;

Let’s take an example for understanding:

Suppose we want to convert a number “2345.67” into TO_BINARY_FLOAT.

SELECT TO_BINARY_FLOAT(2345.67)
FROM dual;

The output of the above statement will be:

TO_BINARY_FLOAT(2345.67)
2346E+003

TO_BINARY_DOUBLE Function in Oracle SQL – PLSQL

by

The TO_BINARY_DOUBLE function in Oracle SQL / PLSQL is used to convert a numeric value into TO_BINARY_DOUBLE.

Syntax for the TO_BINARY_DOUBLE function in Oracle SQL / PLSQL is:

SELECT TO_BINARY_DOUBLE (number)
FROM table_name;
Let’s take an example for understanding:

Suppose we want to convert a number “2345.67” into TO_BINARY_DOUBLE.

SELECT TO_BINARY_DOUBLE(2345.67)
FROM dual;

The output of the above statement will be:

TO_BINARY_DOUBLE (2345.67)
2346E+003

DECODE Function in Oracle SQL – PLSQL

by

The DECODE function in Oracle SQL / PLSQL has the functionality of IF-THEN-ELSE statement.

The Syntax for the DECODE function in Oracle SQL / PLSQL is:

SELECT DECODE(expression,search,result,[search, result]…[default])
FROM table_name;

  • ‘expression’ is the value to be compared
  • ‘search’ is the value to be compared with expression
  • ‘result’ is the value which is returned if the expression matches search
  • ‘default’ is option and is returned if search in not equal to expression. If default is omitted then DECODE will return NULL.

Let’s take an example for understanding:

Suppose we have a table named ‘employee’ as shown below:

Employee_Id Decoded
101 A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Example 1:

Using DECODE in SELECT Statement

Suppose we write our query as:

SELECT employee_id
       ,DECODE(employee_id,101,'A'
       ,102,'B'
       ,103,'C'
       ,104,'D'
       ,'F') Decoded
FROM employee;

We will get the following output:

Employee_Id Decoded
101 A
102 B
103 C
104 D
105 F

The above query is similar to writing

IF employee_id =101 THEN
  Result = A
ELSE IF employee_id = 102 THEN
  Result = B
ELSE IF employee_id = 104 THEN
  Result = C
ELSE IF employee_id = 104 THEN
  Result = D
ELSE
  Result = F

Here we can see that we have used DECODE function as an IN THEN ELSE loop.

NVL2 Function in Oracle SQL – PLSQL

by

The NVL2 function in Oracle SQL / PLSQL enhances the functionality of the NVL function as NVL2 allows us to substitute a value when a NULL is encountered and also when a NULL is not encountered.

The Syntax for the NVL2 function in Oracle SQL / PLSQL is:

SELECT NVL2(string1, replace_with_when_not_null, replace_with_when_null)
FROM table_name;

  • ‘string1’ is the field or column for testing NULL value
  • ‘replace_with_when_not_null’ is the string with which the NOT NULL values will be substituted with.
  • ‘replace_with_when_null’ is the string with which the NULL values will be substituted with.

Let’s take an example for understanding:

Suppose we have a table named ‘employee’ as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000
104 Emp D 30000
105 Emp E 32000

Example 1:

Using NVL2 with numbers and string

Suppose we write our query as:

SELECT employee_id
       ,employee_name
       ,salary
       ,NVL2(department,'Department','No Department')
       ,NVL2(commission,20,0)
FROM employee;

We will get the following output:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Department 20
102 Emp B 20000 Department 20
103 Emp C 28000 No Department 20
104 Emp D 30000 No Department 20
105 Emp E 32000 No Department 20

Here we can see that we have substituted a string and number type in ‘department’ and ‘commission’ columns respectively using the NVL2 function.

Example 2:

Using NVL2 to get the values in records or rows.

Suppose we have a table named ‘employee’ as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000
104 Emp D 30000
105 Emp E 32000

Suppose we write our query as:

SELECT employee_id
       ,employee_name
       ,salary
       ,NVL2(department,department,'No Department')
       ,NVL2(commission,commission,0)
FROM employee;

We will get the following output:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 No Department 0
104 Emp D 30000 No Department 0
105 Emp E 32000 No Department 0

Here we can see that we have retrieved the department names and commission details of the employees as present in the data base and we have also substituted the NULL values in ‘department’ and ‘commission’ columns as ‘No Department’ and ‘0’ wherever NULL was present.

In this case NVL2 is behaving as NVL

NVL Function in Oracle SQL – PLSQL

by

Oracle SQL / PLSQL uses NVL function to substitute a value whenever a NULL is encountered.

Syntax for the NVL function in Oracle SQL / PLSQL is:

SELECT NVL(string1, replace_with)
FROM table_name;

  • ‘string1’ is the field or column for testing NULL value
  • ‘replace_with’ is the string with which the NULL values will be substituted with.

Let’s take an example for understanding:

Suppose we have a table named ‘employee’ as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000
104 Emp D 30000
105 Emp E 32000

Example 1: Using Oracle SQL / PLSQL NVL Function with numbers and string

Suppose we write our query as:

SELECT employee_id
       ,employee_name
       ,salary
       ,NVL(department,'No Department')
       ,NVL(commission,0)
FROM employee;

We will get the following output:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 No Department 0
104 Emp D 30000 No Department 0
105 Emp E 32000 No Department 0

Using the Oracle SQL /PLSQL NVL Function in example above we have substituted a string and number type in ‘department’ and ‘commission’ columns respectively values ‘No Department’ and ‘0’.