Oracle Common Functions Summary

One, character functions
The character function is the most commonly used function in Oracle, so let's see what the character functions are:
Lower (char): Converts a string to a lowercase format.
Upper (char): Converts a string to an uppercase format.
Length (char): Returns the lengths of the strings.
substr (char, M, N): intercepts a substring of a string, n means n characters, not a delegate to Nth
Replace (CHAR1, search_string, replace_string)
InStr (C1,C2,I,J)--Determines whether a character or string exists, returns the index of where it appears, otherwise returns less than 1, searches for the specified character in a string, and returns the location of the specified character;
C1 string to be searched
C2 the string you want to search
I Search start position, default is 1
Where J appears, default is 1

Problem: Display all employees ' names in lowercase
Sql> Select Lower (ename) from EMP;
Issue: Display the names of all employees in uppercase.
Sql> Select Upper (ename) from EMP;
Issue: Displays the name of an employee who is exactly 5 characters.
Sql> SELECT * from emp where length (ename) = 5;
Issue: Displays the first three characters of all employee names.
Sql> Select substr (ename, 1, 3) from EMP;
Issue: The names of all employees are displayed in uppercase letters and lowercase in the following way.
Sql> Select Upper (substr (ename,1,1)) | | Lower (substr (Ename,2,length (ename)-1)) from EMP;
Issue: Displays the names of all employees in lowercase with the first letter, followed by uppercase.
Sql> Select Lower (substr (ename,1,1)) | | Upper (Substr (Ename,2,length (ename)-1)) from EMP;
Problem: Show all employees ' names, replace All "a" with "I am Tiger"
Sql> Select Replace (ename, ' a ', ' I am a tiger ') from EMP;
Question: InStr (Char1,char2,[,n[,m]) usage
Sql> Select InStr (' Azhangsanbcd ', ' Zhangsan ') from dual; --Return 2
Sql> Select InStr (' Oracle traning ', ' Ra ', 1, 1) instring from dual; --Return 2
Sql> Select InStr (' Oracle traning ', ' Ra ', 1, 2) instring from dual; --Return 9
Sql> Select InStr (' Oracle traning ', ' Ra ', 1, 3) instring from dual; --return 0, according to the conditions, due to RA only two times, the fourth parameter 3, that is, the 3rd occurrence of the position of RA, it is clear that the 3rd time is no longer appear, so the result returned 0. Note that a space is also counted as a character
Sql> Select InStr (' abc ', ' d ') from dual; --Return 0

second, mathematical functions
The input parameters and the data type of the return value of the mathematical function are numeric types. Mathematical functions include COS,COSH,EXP,LN, log,sin,sinh,sqrt,tan,tanh,acos,asin,atan,round, etc.
We talk about the most commonly used:
Round (n,[m]) This function is used to perform rounding,
If M is omitted, it is rounded to an integer.
If M is a positive number, it is rounded to the M-bit of the decimal point.
If M is a negative number, it is rounded to the M-bit of the decimal point.
eg, SELECT round (23.75123) from dual; --Return 24
SELECT round (23.75123,-1) from dual; --Return 20
SELECT round (27.75123,-1) from dual; --Return 30
SELECT round (23.75123,-3) from dual; --Return 0
SELECT round (23.75123, 1) from dual; --Return 23.8
SELECT round (23.75123, 2) from dual; --Return 23.75
SELECT round (23.75123, 3) from dual; --Return 23.751
Trunc (N,[m]) This function is used to intercept numbers.
If M is omitted, the fractional part is truncated,
If M is positive, it is truncated to the M-bit of the decimal point,
If M is a negative number, it is truncated to the first m bit of the decimal point.
eg, SELECT trunc (23.75123) from dual; --Return 23
SELECT trunc (23.75123,-1) from dual; --Return 20
SELECT trunc (27.75123,-1) from dual; --Return 20
SELECT trunc (23.75123,-3) from dual; --Return 0
SELECT trunc (23.75123, 1) from dual; --Return 23.7
SELECT trunc (23.75123, 2) from dual; --Return 23.75
SELECT trunc (23.75123, 3) from dual; --Return 23.751
MoD (m,n) take the remainder function
eg, select mod (10,2) from dual; --Return 0
SELECT MOD (10,3) from dual; --Return 1
Floor (n) returns the largest integer less than or equal to n
Ceil (n) returns the smallest integer greater than or equal to n
eg, SELECT ceil (24.56) from dual; --Return 25
SELECT Floor (24.56) from dual; --Return 24
ABS (n) returns the absolute value of the number n
The processing of numbers, in the financial system or the banking system, the most, different processing methods, the financial statements have different results

third, date function
Date functions are used to process data of the date type. By default, the date format is Dd-mon-yy, which is "December-July-12"
(1) Sysdate return to System time
eg, sql> select sysdate from dual;
(2) Oracle Add_months function
The Oracle Add_months (time,months) function can get n months before or after a certain time
eg, select Add_months (sysdate,-6) from dual; --the result of the query is the time before the current time of half a year
Select Add_months (sysdate,6) from dual; --The result of this query is the time after half a year of the current time
(3) Last_day (d): Returns the last day of the month in which the specified date is located
Problem: Find employees who have been in the workforce for more than 8 months
Sql> SELECT * from emp where sysdate>=add_months (hiredate,8);
Question: Displays the name and date of employment of the employee who has been in service for 10 years.
Sql> Select ename, hiredate from EMP where sysdate>=add_months (HIREDATE,12*10);
Issue: For each employee, show the number of days that they joined the company.
Sql> Select Floor (sysdate-hiredate) "Entry Days", ename from EMP;
Or
Sql> Select Trunc (sysdate-hiredate) "Number of days in the job", ename from EMP;
Question: Find out all employees employed on the 3rd day of the month.
Sql> Select Hiredate,ename from emp where Last_day (hiredate) -2=hiredate;

Iv. Conversion Functions
Conversion functions are used to convert data types from one type to another. In some cases, the data type of the Oracle server allow value is different from the actual, when Oracle server implicitly converts the type of conversion data
Like what:
CREATE TABLE t1 (id int);
INSERT into T1 values (' 10 ');--so that Oracle will automatically '-->10 '
CREATE TABLE t2 (ID varchar2 (10));
INSERT into T2 values (1); -So Oracle will automatically 1--' 1 ';
What we're saying is that while Oracle can do the implicit conversion of the data type, it doesn't fit all, and to improve the reliability of the program, we should use a conversion function.

To_char () function
You can use select Ename, HireDate, Sal from emp where deptno = 10; Display information, however, in some cases, this does not meet your needs.
Question: Whether the date can be displayed in/minutes/sec
Sql> Select Ename, To_char (hiredate, ' Yyyy-mm-dd hh24:mi:ss ') from EMP;
Question: Whether the salary can display the specified currency symbol
Sql>
YY: Two-digit year 2004-->04
YYYY: Four-digit year 2004
MM: Two-digit month August-->08
DD: Two-digit day number 30th-->30
Hh24:8 Point-->20
Hh12:8 Point-->08
MI, ss--> display min \ sec
9: Displays the number and ignores the previous 0
0: Displays the number, if the number of digits is insufficient, then uses 0 to be padded
.: Displays the decimal point at the specified location
,: Displays a comma at the specified location
$: Before the number plus USD
L: Add a local currency symbol in front of the number
C: Add the International currency symbol in front of the number
G: Displays the group separator at the specified position,
D: Displays the decimal symbol (.) at the specified position.

Question: When the salary is displayed, add the local currency unit to the front
Sql> Select Ename, To_char (hiredate, ' yyyy-mm-dd hh24:mi:ss '), To_char (Sal, ' L99999.99 ') from EMP;
Issue: Show all employees who have been in employment in 1980
Sql> SELECT * from emp where TO_CHAR (hiredate, ' yyyy ') = 1980;
Issue: Show all employees who have been in employment in December
Sql> SELECT * from emp where TO_CHAR (hiredate, ' mm ') = 12;

To_date () function
The function to_date is used to convert a string into a date type of data.
Question: Can I add a date in the way the Chinese are accustomed to the year-month-day.
eg, SELECT to_date (' 2012-02-18 09:25:30 ', ' yyyy-mm-dd hh24:mi:ss ') from dual;

V. Sys_context () system functions
1) Terminal: The identifier of the terminal that the current session client corresponds to, such as the computer name
2) Language: Language
3) db_name: Current database name
4) Nls_date_format: the date format that the current session customer corresponds to
5) Session_user: The database user name for the current session client
6) Current_schema: The default scheme name corresponding to the current session customer
7) Host: Returns the name of the host where the database resides
With this function, you can query some important information, such as which database you are using?
Select Sys_context (' USERENV ', ' db_name ') from dual;
Note: Userenv is fixed, cannot be changed, db_name can be replaced by other,
eg, select Sys_context (' USERENV ', ' language ') from dual;
Select Sys_context (' USERENV ', ' Current_schema ') from dual;

See also: http://www.cnblogs.com/linjiqin/archive/2012/02/07/2341925.html

Oracle Common Functions Summary