Some unique functions in Oracle

In ms SQL Server, I am used to writing SQL statements. I suddenly need to write SQL statements in Oracle. To tell the truth, there is no big difference in principle. However, some functions in Oracle are not used unless I understand them, sometimes it is quite troublesome. I am going to write a small summary to list some Oracle functions involved in the project. On the one hand, it is convenient for me to find them later (I am too old to use my head, huh, huh ); on the other hand, we also hope to give you a convenient way to learn from each other and learn from each other through discussion. Started:

1. nullif ()

Select ename,Nullif(Comm, 0) Comm from EMP;
Ename comm
--------------------
Smith
Allen 300
Ward 500
Jones
Martin 1400
Blake
Clark
Scott
At first glance, this function knows what it means. If comm = 0, it can be replaced by null in the result set.

2. coalesce ()

select ename, coalesce (Comm, 0) Comm from EMP;
ename comm
----------
Smith 0
Allen 300
ward 500
Jones 0
Martin 1400
blke 0
Clark 0
Scott 0
this function is similar to the last one, however, you can replace any value that you want to display in the result set.

Select ename, coalesce (to_char (Comm), 'None') Comm from EMP;
Ename comm
--------------------
Smith none
Allen 300
Ward 500
Jones none
Martin 1400
Blake none
Clark none
Scott none
Let's see it.

3. nvl ()

This function is relatively simple, that is, to determine whether it is null, you can set a value, such as nvl (A,), that is, if A is empty, 0 is displayed; otherwise, 1 is displayed.

4.Lpad () and rpad ()

SelectLpad(Ename, 10 ,'*'),Rpad(Ename, 10, '>') from EMP;

Lpad (ename, 10, '*') rpad (ename, 10, '> ')
----------------------------------------
* *** Smith >>>>
***** Allen >>>>>
* ***** Ward >>>>>>
* *** Jones >>>>>
* *** Martin >>>>
* ***** Blake blke >>>>
* *** Clark >>>>>
* *** Scott >>>>
* ***** King king King >>>>>>
* *** Turner >>>>
* *** Adams >>>>

Lpad (ename, 10, '*') rpad (ename, 10, '> ')
----------------------------------------
* ***** James >>>>
* ***** Ford >>>>>>
* *** Miller >>>>

14 rows have been selected.
From the results, we can see that the lpad () function is used to indicate that if the column length does not reach the specified value, no corresponding character is displayed on the left of the column. The rpad () function is completed to the right.

5. Greatest () and least ()

Select greatest (, 5), least (,) from dual

Greatest (, 5) least)
-------------------------------------
5 0

The result shows that the greatest () function is used to find the largest value in the list, and least () is used to find the smallest value in the list.

Find a time to continue expansion. I hope you can help me continue expansion.