Introduction to common ORACLE numeric functions, conversion functions, and string functions

Note: N indicates the numeric type, C indicates the numeric type, D indicates the date type, [] indicates that parameters can be ignored, and fmt indicates the format.

A single value function returns a single value in a query. It can be applied to select, where clause, start with, connect by clause, and having clause.
(1). Numeric Functions (Number Functions)
Numeric functions enter numeric parameters and return numeric values. The return values of most such functions support 38 decimal places, such as COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH. ACOS, ASIN, ATAN, and ATAN2 support 30 decimal places.

1. MOD (n1, n2) returns the remainder of n1 except n2. If n2 = 0, the value of n1 is returned.
Example: select mod (24, 5) from dual;

2. ROUND (n1 [, n2]) returns the value of n1 after n2 to the right of the decimal point. The default value of n2 is 0, if n2 is a negative number, it is rounded to the corresponding digit on the left of the decimal point (although the n2 value mentioned in oracle documents must be an integer, in fact, the judgment here is not rigorous, even if n2 is not an integer, it will also automatically perform processing after the n2 is rounded up. However, we need to pay special attention to other points that must be rounded up in my document. If not executed in an integer, an error will be reported ).
Example: select round (23.56), ROUND (23.56, 1), ROUND (23.56,-1) from dual;

3. TRUNC (n1 [, n2] returns the n1 value from the end to the n2 decimal point. The default value of n2 is 0, when n2 is set by default, the end of n1 is taken as an integer. If n2 is a negative value, the end is taken at the corresponding digit on the left of the decimal point.
Example: select trunc (23.56), TRUNC (23.56, 1), TRUNC (23.56,-1) from dual;

(2) Character Functions Returning Character Values)
This type of function returns the same type as input.
The length of the CHAR type value returned by limit cannot exceed 2000 bytes;
The VCHAR2 type value returned by the secret cannot exceed 4000 bytes;
If the length of the characters to be returned exceeds the upper limit, oracle does not report an error but directly truncates it to the maximum supported length.

The CLOB type value returned by the sequence cannot exceed 4 GB;
For CLOB-type functions, if the returned value is too long, oracle will not return any errors but directly throw an error.

1. LOWER (c) converts the characters in the specified string to lowercase. The types of CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, and NCLOB are supported.
Example: select lower (WhaT is tHis) from dual;

2. UPPER (c) converts the characters in the specified string to uppercase. The types of CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, and NCLOB are supported.
Example: select upper (WhaT is tHis) from dual;

3. LPAD (c1, n [, c2]) returns a string with a length of = n. Note the following points:
Limit if n <c1.length, the specified length is truncated from right to left;
If n> c1.length and c2 is null, the string length is increased from left to right with a space and n is returned;
If n> c1.length and c2 is not null, supplement c1 length to n from left to right with the specified character c2 and return;
Example: select lpad (WhaT is tHis, 5), LPAD (WhaT is tHis, 25), LPAD (WhaT is tHis, 25,-) from dual;
Finally, let's just guess what would happen if n <0

4. RPAD (c1, n [, c2]) returns a string with a specified length of = n, which is basically the same as the upper limit. However, the supplementary character is opposite to the upper limit from the right to the left;
Example: select rpad (WhaT is tHis, 5), RPAD (WhaT is tHis, 25), RPAD (WhaT is tHis, 25,-) from dual;

5. TRIM ([[LEADING | TRAILING | BOTH] c2 FROM] c1) Haha, I am confused by the invincible way, the figure is clearer here.
It looks complicated and easy to understand:
If no parameter is specified, oracle removes the leading and trailing spaces of c1.
Example: select trim (WhaT is tHis) from dual;
If the c2 parameter is specified for explain, oracle removes the c1 header and tail c2 (this is recommended for careful testing, there are many different situations)
Example: select trim (w from WhaT is tHis w) from dual;
If the leading parameter is specified, the c1 header c2 is removed.
Example: select trim (leading w from WhaT is tHis w) from dual;
If the trailing parameter is specified, the c2 at the end of c1 is removed.
Example: select trim (trailing w from WhaT is tHis w) from dual;
If the both parameter is specified, the c1 header and tail c2 will be removed (is it different from the unspecified one? No difference !)
Example: select trim (both w from WhaT is tHis w) from dual;

Note: c2 length = 1

6. The LTRIM (c1 [, c2]) Ten millions of tables think it is similar to the long image above, and its functions are similar to those above, this function removes the same characters from the left side of string c1 as the specified string c2 and returns the result. If c2 is null, spaces are truncated by default.
Example: select ltrim (WWhhhhhaT is tHis w, Wh) from dual;

7. RTRIM (c1, c2) is the same as above, but in the opposite direction
Example: select rtrim (WWhhhhhaT is tHis w W, W w) from dual;

8. REPLACE (c1, c2 [, c3]) replaces c2 with c3 in the c1 string. If c3 is null, all c2 are deleted from c1.
Example: select replace (WWhhhhhaT is tHis w W, W,-) from dual;

9. SOUNDEX (c) magic function. This function returns the voice representation of string parameters. It is very useful for comparing different words with the same pronunciation. The algorithm for computing speech is as follows:
The delimiter retains the first letter of the string, but deletes a, e, h, I, o, w, and y.
Assign the numbers in the following table to the corresponding letters:
1: B, f, p, v
2: c, g, k, q, s, x, z
3: d, t
4: l
5: m, n
6: R
If the string contains two or more letters with the same number (for example, B and f), or only h or w, the other strings are deleted, only one retained;
Limit returns only the first 4 bytes, not enough to fill with 0
Example: select soundex (dog), soundex (boy) from dual;

10. SUBSTR (c1, n1 [, n2]) intercepts a string of the specified length. A function may be full of traps if you don't pay attention to it.
N1 = start length;
N2 = the length of the intercepted string. If it is null, It is truncated to the end of the string by default;
If n1 = 0 then n1 = 1
If n1> 0, oracle confirms the starting position from left to right.
Example: select substr (What is this, 5, 3) from dual;
If n1 is <0, oracle confirms the start position from right to left.
Example: select substr (What is this,-5, 3) from dual;
Returns NULL if n1> c1.length
Example: select substr (What is this, 50, 3) from dual;
Then you can guess how to return the value if n2 is less than 1.

11. In terms of functions, this function is similar to replace. However, it should be noted that the translate is an absolute match replacement, which is very different from the replace function. What is absolute match replacement? Simply put, it is to replace string c1 with C3. If the text description is still incomprehensible, we can use several examples to explain:
For example:
Select translate (What is this,-) from dual;
Select translate (What is this,-,) from dual;
Results are empty. To try this:
Select translate (What is this,) from dual;
Let's take a look at this:
Select translate (What is this, ait,-*) from dual;
Do you understand? The Replace function is easy to understand. It replaces a specified character in a string with other characters, and its characters must be continuous. In translate, It is the c2 that appears in the specified string c1. Replace the characters in c2 with the characters in c3 in the same position order as those in c3. Understand? Replace is replaced, while translate is like Filtering

(3) Character Functions Returning Number Values)
This function supports all data types.

1. INSTR (c1, c2 [, n1 [, n2]) returns the position of c2 in c1.
Original c1: original string
Limit c2: string to be searched
Distinct n1: Start position of the query. positive values indicate left-to-right, and negative values indicate right-to-left (size indicates position, for example, 3 indicates 3rd on the left, -3 indicates starting at 3rd on the right ). Black and black. If it is 0, 0 is returned.
Limit n2: Number of matching items. Greater than 0
Example: select instr (abcdefg, e,-3) from dual;

2. LENGTH (c) returns the LENGTH of the specified string. If
Example: select length (in A123) from dual;
Guess the return value of select length () from dual;

(4). Date Functions (Datetime Functions)
In this function, except months_between, all values return a date.

1. ADD_MONTHS () returns the value after the specified date month + n. n can be any integer.
Example: SELECT ADD_MONTHS (sysdate, 12), ADD_MONTHS (sysdate,-12) from dual;

2. CURRENT_DATE: returns the default time in the time zone of the current session.
For example:
SQL> alter session set nls_date_format = mm-dd-yyyy;
SQL> select current_date from dual;

3. If the SYSDATE function is the same as the previous function, the default time of the current session time zone is returned. However, if the time obtained by using both sysdate and current_date is not necessarily the same, current_date will be one second faster than sysdate in some cases. After a short communication with xyf_tck (the working mechanism of the master station's masterpiece ORACLE is well written, in simple terms), we think current_date is the return result after rounding the milliseconds in current_timestamp, although the document support is not found, it should be different. At the same time, there may be only one second of error in some cases. Generally, this will not affect your operations.
Example: select sysdate, CURRENT_DATE from dual;

4. LAST_DAY (d) returns the last day of the month of the specified time.
Example: SELECT last_day (SYSDATE) from dual;

5. NEXT_DAY (d, n) returns the date of the first n after the specified date, and n is a day of the week. However, it should be noted that if n is a character, its week format must be the same as the week format in the default time zone of the current session.
For example, the CHINESE nt used by sthink, and the value of nls_language is simplified chinese.
SELECT NEXT_DAY (SYSDATE, 5) from dual;
SELECT NEXT_DAY (SYSDATE, Thursday) from dual;
Both methods can get the correct response,:
SELECT NEXT_DAY (SYSDATE, Thursday) from dual;