Oracle Common Functions Summary

In the Oracle OCP exam, a significant portion of the knowledge points involves an examination of common Oracle functions. Although Oracle's Official document SQL Language reference in the functions chapter lists all of the Oracle's own functions, but if you want to look at the system, it will take considerable effort, not to mention the English. If you encounter an unfamiliar, check it, not often used, and easily forgotten. The following is a simple collation of Oracle common functions, easy to query later.

First, uppercase and lowercase conversion functions

1. Lower function

The lower function outputs characters in lowercase. Its syntax is lower (char).

For example:

Sql> Select Lower (100+100), Lower (sysdate), lower (' HELLO ') from dual;

Low LOWER (SYS LOWER
---    ---------      -----
25-sep-14 Hello

Note: All returned are character types, the data and date expressions are evaluated before the lower function is executed, and are implicitly converted to character data.

2. Upper function

The upper function outputs characters in uppercase. Its syntax is upper (char).

For example:

Sql> Select upper (' Hello World ') from dual;

UPPER (' HELL
-----------
HELLO World

3. Initcap function

The Initcap function capitalizes the first letter of each word in a string, and the remaining letter of the word is lowercase.

For example:

Sql> Select Initcap (' Hello world! that's funny! ') from dual;

Initcap (' helloworld! Thatisf
---------------------------
Hello world! That is funny!

Second, character manipulation function

1. Concat function

The Concat function is used to concatenate two of strings. There are two parameters, the syntax is concat (S1,S2).

For example:

Sql> Select concat (' Hello ', ' world ') from dual;

CONCAT (' HE
----------
HelloWorld

Sql> Select Concat (concat (ename, "s job is"), job) from EMP where empno=7788;

CONCAT (CONCAT (ename, "Sjobis
-----------------------------
SCOTT ' s job is ANALYST

2. Length function

The length function returns the number of characters in a string, including spaces, tabs, and special characters. Its syntax is length (s).

For example:

Sql> Select Length (' Hello world! ') from dual;

LENGTH (' helloworld! ')
---------------------
12

3. Lpad and Rpad functions

The Lpad and Rpad functions are called left padding and right fill functions, respectively. They fill the specified number of characters to the left or right of the given string, respectively. Its syntax is Lpad (s,n,p) and Rpad (S,n,p), where s represents the source string, n represents the final length of the returned string, and P specifies the string to use for padding. Note: If the parameter n is less than or equal to the length of the source string s, no characters are added, and only the first n characters of the source string s are returned. P defaults to a space.

For example:

Sql> Select Lpad (' Hello ', ' ten, ' * '), lpad (' Hello ', ' ten ', ' *# '), Lpad (' Hello ', ' ten '), Lpad (' Hello ', 4, ' * ') from dual;

Lpad (' HELL lpad (' HELL lpad (' HELL lpad
----------     ----------      ----------    ----
Hello *#*#*hello Hello Hell

Sql> Select Rpad (' Hello ', ' ten, ' * '), rpad (' Hello ', ' ten ', ' *# '), Rpad (' Hello ', ' ten '), Rpad (' Hello ', 4, ' * ') from dual;

Rpad (' HELL rpad (' HELL rpad (' HELL rpad
----------     ----------       ----------     ----
hello***** hello*#*#* Hello Hell

4. Trim function

The trim function removes some characters from the beginning or end of a string. Its syntax is trim ([Trailing|leading|both] trimstring from S). which

TRIM (s) Delete spaces on both sides of the input string

TRIM (trailing trimstring from s) removes all trimstring (if any) from the end of the string S.

TRIM (leading trimstring from S) removes all trimstring from the beginning of the string s.

TRIM (both trimstring from s) removes all trimstring from the beginning and end of the string S.

Sql> Select Trim (both ' * ' from ' ***hello**** ') from dual;

TRIM (
-----
Hello

Sql> Select trim (Trailing ' * ' from ' ***hello**** ') from dual;

TRIM (TRA
--------
Hello

Sql> Select trim (Leading ' * ' from ' ***hello**** ') from dual;

TRIM (lead
---------
hello****

Note: Trimstring can only be one character.

Sql> Select trim (Leading ' *! ' from ' *!*!hello ') from dual;
Select Trim (Leading ' *! ' from ' *!*!hello ') from dual
*
ERROR at line 1:
Ora-30001:trim set should has only one character

5. InStr function (in-string)

The InStr function determines the position of the search string within the given string. It returns the numeric position, and if the search string does not exist, the function returns 0. Its syntax is InStr (source String,search string,[search start position],[nth occurrence]). Where the latter two parameters are optional, the default value of search start position is 1, which is the beginning of the source string. The default value for nth occurrence is 1, which is the first occurrence.

For example:

Sql> Select InStr (' 1*3*5*7*9 ', ' * ', -3,2) from dual;

INSTR (' 1*3*5*7*9 ', ' * ', -3,2)
---------------------------
4

Sql> Select InStr (' 1*3*5*7*9 ', ' * ') from dual;

INSTR (' 1*3*5*7*9 ', ' * ')
----------------------
2

Sql> Select InStr (' 1*3*5*7*9 ', ' * ', 2) from dual;

INSTR (' 1*3*5*7*9 ', ' * ', 2)
------------------------
2

6. substr function (Substring)

The SUBSTR function extracts a substring of the specified length, starting at the given position in the given source string. Its syntax is substr (source String,start position,[number of characters to Extrace]). The last parameter is optional, and if not specified, the default number of characters from start position to the end of the source string.

For example:

Sql> Select substr (' 1*3*5*7 ', 3) from dual;

SUBST
-----
3*5*7

Sql> Select substr (' 1*3*5*7 ', 3,2) from dual;

Su
--
* *

Sql> Select substr (' 1*3*5*7 ', -3,2) from dual;

Su
--
5*

7. Replace function

The Replace function replaces all occurrences of the search item in the source string with a replacement. Its syntax is replace (source String,search item,[replacement term]). Replacement term defaults to null characters.

For example:

sql> Select replace (' 1*3*5*7 ', ' * ', ' and ') from dual;

REPLACE (' 1
----------
1->3->5->7

sql> Select replace (' 1*3*5*7 ', ' * ') from dual;

Repl
----
1357

Three, number function

1. Digital round function

The round function rounds a numeric value based on the specified fractional precision. Its syntax is round (source Number,decimal precision). The decimal precision parameter specifies the precision of rounding. The default is 0, which is rounding the source number to an integer. If the specified decimal precision n is a positive number, the valid digits to be rounded are located to the right of the decimal point (n+1). If n is negative, the number of valid digits to be rounded is n places to the left of the decimal point.

For example:

Sql> Select Round (145.78,-1) from dual;

ROUND (145.78,-1)
----------------
150

Sql> Select Round (145.78) from dual;

ROUND (145.78)
-------------
146

Sql> Select Round (145.78,1) from dual;

ROUND (145.78,1)
---------------
145.8

2. Trunc function (Truncate)

The trunc function intercepts numeric values based on the specified fractional precision. Its syntax is trunc (source Number,decimal precision).

For example:

Sql> Select Trunc (145.78,-1) from dual;

TRUNC (145.78,-1)
----------------
140

Sql> Select Trunc (145.78) from dual;

TRUNC (145.78)
-------------
145

Sql> Select Trunc (145.78,1) from dual;

TRUNC (145.78,1)
---------------
145.7

3. MoD function (modulus)

The MoD function returns the remainder of the division operation.

For example:

Sql> Select mod (5,2) from dual;

MOD (5,2)
----------
1

Sql> Select mod (5.2,2.2) from dual;

MOD (5.2,2.2)
------------
.8

Four, Date function

1. Months_between function

The Months_between function returns a numeric value for the number of months between two date parameters. Its syntax is Months_between (DATE1,DATE2). The function calculates the difference in the month between Date1 and Date2 (31 days per month), which is date1-date2. The return value may consist of integers and fractional parts. Where the integer represents the number of months between the two dates. the number of decimal points indicates the number of days and times remaining, based on the 31-day month.

For example:

Sql> Select Months_between (sysdate,sysdate-31) from dual;

Months_between (SYSDATE,SYSDATE-31)
----------------------------------
1

Sql> Select Months_between (' 2-mar-2014 ', ' 1-feb-2014 ') from dual;

Months_between (' 2-mar-2014 ', ' 1-feb-2014 ')
-----------------------------------------
1.03225806

Sql> select 1/31 from dual;

1/31
----------
.032258065

--you can see that the fractional part is based on 31 days.

2. add_months function

The Add_months function syntax is add_months (Date,number of MONTHS), which calculates the date when the specified month is added to the specified date. The number of months can be negative.

For example:

Sql> Select Add_months (' 1-mar-2014 ', 1) from dual;

Add_month
---------
01-apr-14

Sql> Select Add_months (' 1-mar-2014 ', 2.9) from dual;

Add_month
---------
01-may-14

Sql> Select Add_months (' 1-mar-2014 ', -2.9) from dual;

Add_month
---------
01-jan-14

3. Next_day function

Next_day returns the day of the week after the specified date. Its syntax is next_day (start date,day of the week). The function calculates the date of day of the week next occurrence after start date. Day of the week can be either a character value or an integer value. Where the character value is at least the first three characters of the week name, for example, Sunday can be expressed as Sun,sund,sunda or Sunday. For integers, 1 means Sunday, 2 means Monday, and so on.

For example: 19-sep-2014 is Friday

Sql> Select Next_day (' 19-sep-2014 ', 5) from dual;

Next_day (
---------
25-sep-14

Sql> Select Next_day (' 19-sep-2014 ', ' Tue ') from dual;

Next_day (
---------
23-sep-14

Sql> Select Next_day (' 19-sep-2014 ', ' SUNDAY ') from dual;

Next_day (
---------
21-sep-14

4. Last_day function

The Last_day function extracts the month to which the specified date belongs, and calculates the date of the last day of the month. Its syntax is last_day (date).

For example:

Sql> Select Last_day (' 19-sep-2014 ') from dual;

Last_day (
---------
30-sep-14

5. Date round function

The date round function rounds the date according to the specified date precision format. Its syntax is round (source date,[date Precision format]). The source date represents any date. The date precision format includes century (CC), year (YYYY), Quarter (Q), month (MM), Week (W), Day (DD), Time (HH) and Min (MI).

If the day part is greater than 16, it will enter the next one months, otherwise the month. If the month is between 1 and 6, the date that starts at the beginning of the year is placed, otherwise the date begins in the next year.

For example:

The query time is:2014-09-26 04:44:38

Sql> Select Round (sysdate) day,round (sysdate, ' W ') Week,round (sysdate, ' month ') month,round (Sysdate, ' year ') year from dual;

Day WEEK MONTH
---------     ---------     ---------      ---------
26-sep-14 29-sep-14 01-oct-14 01-jan-15

Because the query time is 4 points, did not check 12 points, it is day. Because this month 1th is Monday, week returns the date of the query, the next Monday, that is, 29-sep-14. Because the query time is 26th, more than 16th, so month needs to enter the next one months. Because the query time is September, more than June, so into the next year.

6. Date Trunc function

The date trunc function intercepts the date according to the specified date precision format. Its syntax is trunc (source date,[date Precision format]). The date precision format Specifies the precision of the interception, and the default intercept precision is day. This means that all the time of the source date is set to 00:00:00 (00 o'clock, 00 minutes, and 00 seconds). Interception at the month level sets the date of the source date to the first day of the month. Grade interception returns the date of the beginning of the year.

For example:

Sql> Select Trunc (sysdate) day,trunc (sysdate, ' W ') Week,trunc (sysdate, ' month ') Month,trunc (Sysdate, ' year ') year from dual;

Day WEEK MONTH
---------     ---------     ---------     ---------
26-sep-14 22-sep-14 01-sep-14 01-jan-14

The only thing to be aware of IS week, since this month's 1th is Monday, and week returns the date of this week's Monday.

Oracle Common Functions Summary