Step-by-Step Oracle functions (Focus)

Single-row functions-character functions although each database supports SQL statements, each database also has the operation functions supported by each database. These are single-row functions. If you want to develop a database

Single-row functions-character functions although each database supports SQL statements, each database also has the operation functions supported by each database. These are single-row functions. If you want to develop a database

Single-line functions-character Functions

Although each database supports SQL statements, each database also has operation functions supported by each database. These are single-row functions. If you want to develop a database, in addition to SQL, you need to learn more functions.

Single-row functions can be divided into the following types: character functions, numeric functions, date functions, conversion functions, and general functions;

1-character function:

Character functions are mainly used to operate string data. Below are several character functions:

* UPPER (string | column): converts the input string to uppercase and returns the result;

* LOWER (string | column): converts the input string to lowercase and returns the result;

* INITCAP (string | column): starts with an uppercase letter;

* LENGTH (string | column): returns the LENGTH of the string;

* REPLACE (string | column): REPLACE;

* SUBSTR (string | column, start point [end point]): String truncation:

It is a little troublesome in Oracle. Even if you want to verify the string, you must write a complete SQL statement. Therefore, for the convenience of your query, therefore, a "dual" virtual table is provided.

Example 1: Observe the function for converting to uppercase

Select upper ('hello') FROM dual;

Example 6: Use the letter "_" to replace the information of all the letters "A" in the name;

Col REPLACE (ename, 'A', '_') FORMAT A50; select replace (ename, 'A', '_') FROM emp;

String truncation has two syntaxes:

|-Syntax 1: SUBSTPR (string | column, start point), which indicates that the string is truncated from the start point to the end;

|-Syntax 2: SUBSTPR (string | column, start point, end point), which indicates intercepting part of content from the start point to the end point;

Example 7: Syntax 1: SUBSTPR (string | column, start point). a vm in hong kong indicates a forward slash from the start point to the end;

SELECT ename, SUBSTR (ename, 3) FROM emp; // starts FROM 3rd characters to the end!

Example 8: syntax 2: SUBSTPR (string | column, start point, end point), which indicates intercepting part of the content from the start point to the end point;

SELECT ename, SUBSTR (ename,) FROM emp; // intercept the first three characters! SELECT ename, SUBSTR (ename, 1, 3) FROM emp; // intercept the first three characters!

Example 9: The last three letters of each employee's name are required;

* Normal idea: Determine the start point through length-2

SELECT ename, SUBSTR (ename, LENGTH (ename)-2) FROM emp;

* New Idea: set a negative number to specify the truncation position from the back.

SELECT ename, SUBSTR (ename,-3) FROM emp;

Interview Questions:

1. When the SUBSTR () function is intercepted, does the subscript start from 0 or 1?

* In the Oracle database, the SUBSTR () function is the same from 0 or 1 (see example 8)

* The SUBSTR () function can also be set to a negative number, indicating that the start point of the truncation is specified later. (see example 9)

2-numeric Functions
There are three numeric functions:

* ROUND (number | column [, number of digits to retain decimal places]): rounding operation;

* TRUNC (number | column [, number of digits retained]): discard the content at the specified position;

* MOD (number 1, number 2): modulo and remainder;

Example 10: Verify the ROUND () function

Select round (903.5) FROM dual;

Select round (903.5), ROUND (-903.53567) FROM dual; select round (903.5), ROUND (-903.53567), ROUND (903.53567,-1) FROM dual; select round (903.5), ROUND (-903.53567), ROUND (903.53567,-1), ROUND (903.53567, 2) FROM dual; // retain 2 digits

Example 11: Verify the TRUNC () function

Select trunc (903.5), TRUNC (-903.53567), TRUNC (903.53567,-1), TRUNC (903.53567, 2) FROM dual;

Example 12: Verify the MOD () function

Select mod (10, 3) FROM dual;

The above three major mathematical functions will also be matched in learning Java!

2-date functions

If you want to perform a date operation, you must first solve the problem of how to obtain the current date, which can be obtained using "SYSDATE". The Code is as follows:

Select sysdate from dual;

Example 13: In addition to the preceding current date, some calculations can also be performed on the date:

* Date + number = date, indicating the date after several days;

Select sysdate + 3, SYSDATE + 300 FROM dual;

* Date-number = date, indicating the date before several days;

SELECT the SYSDATE-3, SYSDATE-300 FROM dual;

* Date-date = number indicates the number of days of two dates, but it must be a big date-a small date;

Example 14: Find the number of employment days for each employee until today;

SELECT ename, hiredate, SYSDATE-hiredate FROM emp;

Note: In many programming languages, we also propose a concept that dates can be expressed by numbers!

In addition to the above three formulas, the website space also provides the following four operation functions:

* LAST_DAY: returns the last day of the specified date;

Example 15: Find the date of the last day of the month

SELECT LAST_DAY (SYSDATE) FROM dual;

* NEXT_DAY (date, number of weeks): calculates the date of X in the next specified Week;

Example 16: Find the next Monday

SELECT NEXT_DAY (SYSDATE, 'monday') FROM dual;

* ADD_MONTHS (date, number): returns the date after several months;

Example 17: Find the time after 4 months

SELECT ADD_MONTHS (SYSDATE, 4) FROM dual;

* MONTHS_BETWEEN (date 1, date 2): returns the month between two dates;

Example 18: Find the month of employment for each employee until today;

SELECT ename, hiredate, MONTHS_BETWEEN (SYSDATE, hiredate) FROM emp; SELECT ename, hiredate, TRUNC (MONTHS_BETWEEN (SYSDATE, hiredate) FROM emp;

We recommend that you use the above functions for date operations during all development, because these functions can avoid the leap year problem.

4-conversion functions (CORE)

Currently, three types of data in the Oracle database are available: NUMBER, VACHAR2, and DATE ), the main function of the conversion function is to complete the conversion operations between these types of data. There are three types of conversion functions:

* TO_CHAR (string | column, Format String): converts a date or number to a string for display;

* TO_DATE (string, Format String): converts the string to DATE for data display;

* TO_NUMBER (string): converts a string to a number for display;

1. TO_CHAR () function

Query the current system date and time before:

Select sysdate from dual;

The format is displayed in the format of "day-month-year". Obviously, this format does not conform to the normal idea. Normally, the format is "year-month-day ",