Common Oracle numeric functions, conversion functions, and string functions

This article will introduce some interesting functions that trisi often uses in daily life, or which are rarely used. There are two types of introduction:
Famous functions-frequently used functions
Non-famous functions-although rarely used, they are very practical in some cases.

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 returned char value cannot exceed 2000 bytes;
The returned vchar2 value 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 length of the returned clob value 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:
If n <c1.length, the specified length is truncated from right to left;
If n> c1.length and C2 is null, the length of the characters from left to right is increased to N with a space and the return value 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, 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 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 W') from dual;
If the trailing parameter is specified, C2 at the end of C1 is removed.
Example: Select trim (trailing 'W' from 'What is this w W') from dual;
If the both parameter is specified, C1 head and tail C2 will be removed (is it different from unspecified? No difference !)
Example: Select trim (both 'W' from 'What is this w 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 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. Computing speechAlgorithmAs follows:
The first letter of the string is retained, but a, e, H, I, O, W, and Y are deleted.
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;
Only the first 4 bytes are returned, and the value is not filled with 0.
Example: Select soundex ('Dog'), soundex ('boys') 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 will confirm 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;
If N1> c1.length, null is returned.
Example: Select substr ('What is this ',) 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.
C1: original string
C2: string to be searched
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 starting from 3rd on the left and-3 indicates starting from 3rd on the right ). Black and black. If it is 0, 0 is returned.
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 ('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;
An error occurs. You are advised that the day of the week is invalid. This is the reason.

6. months_between (D1, D2) returns the month difference between D1 and D2. The result can be positive or negative depending on the value of D1 and D2. Of course, it may also be 0.
For example:
Select months_between (sysdate, sysdate ),
Months_between (sysdate, add_months (sysdate,-1 )),
Months_between (sysdate, add_months (sysdate, 1 ))
From dual;

7. Round (d [, FMT]) introduced round in the previous section about numeric functions. The functions are basically similar here, but the date is used here. If the FMT parameter is not specified, the date closest to the specified date is returned by default.
Example: Select round (sysdate, 'hh24') from dual;

8. trunc (d [, FMT]) has the same principle as the preceding numeric trunc, but it is also the date type of the operation.
Example: Select trunc (sysdate, 'hh24') from dual;

(5). conversion functions (conversion functions)
A Conversion Function converts a specified character from one type to another. Generally, this type of function follows the following conventions: the function name follows the type to be converted and the output type.

1. to_char () this function can be divided into three classes:
Conversion character-> character to_char (c): converts nchar, nvarchar2, clob, and nclob to Char;
Example: Select to_char ('abbcc ') from dual;

Conversion time-> character to_char (d [, FMT]): converts the specified time (data, timestamp, timestamp with Time Zone) to the varchar2 type according to the specified format;
Example: Select to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss') from dual;

Convert value-> character to_char (N [, FMT]): Convert the specified value n to the varchar2 type according to the specified format FMT and return it;
Example: Select to_char (-100, 'l99g999d99mi ') from dual;

2. to_date (C [, FMT [, NLS]) converts char, nchar, varchar2, and nvarchar2 to the date type. If the FMT parameter is not empty, the conversion is performed according to the specified format in FMT. Note the FMT parameter here. If FTM is 'J', it is converted according to the period (Julian day). C must be a positive integer greater than 0 and less than 5373484.
For example:
Select to_date (2454336, 'J') from dual;
Select to_date ('2017-8-23 23:25:00 ', 'yyyy-mm-dd hh24: MI: ss') from dual;

Why must C be no greater than 5373484 in the ad system? Because the value range of Oracle date type is from January 1, January 1, 4712 BC to January 1, December 31, 9999 AD. Take a look at the following statement:
Select to_char (to_date ('1970-12-31 ', 'yyyy-mm-dd'), 'J') from dual;

3. to_number (C [, FMT [, NLS]) converts char, nchar, varchar2, and nvarchar2 strings into numerical values in the format specified in FMT and returns the results.
Example: Select to_number ('-100.00', '9g999d99') from dual;

(6) other auxiliary functions (Miscellaneous single-row functions)

1. Decode (exp, S1, R1, S2, R2 .. s, R [, Def]) can be understood as an enhanced if else, but it does not use multi-line statements, but implements the if else function in a function.
Exp is used as the initial parameter. S is used as the comparison value. If S is the same, R is returned. If s has multiple values, all s are continuously traversed until a certain condition is true. Otherwise, the default def value (if specified) is returned ), if no default value exists and none of the preceding comparisons are true, null is returned.
There is no doubt that decode is a very important function and will be used to implement functions such as row-to-column conversion. It must be kept in mind and used skillfully.

Example: Select decode ('a2 ', 'a1', 'true1', 'a2 ', 'true2', 'default') from dual;

2. Greatest (N1, N2,... n) returns the maximum value in the sequence.
Example: Select greatest (15,5, 75,8) "greatest" from dual;

3. Least (N1, N2.... n) returns the minimum value in the sequence.
Example: Select least (15,5, 75,8) least from dual;

4. nullif (C1, C2)
Nullif is also an interesting function. Logic is equivalent to: case when C1 = c2 then null else C1 end
Example: Select nullif ('A', 'B'), nullif ('A', 'A') from dual;

5. The nvl (C1, C2) logic is equivalent to If C1 is null then C2 else C1 end. C1 and C2 can be of any type. If the two types are different, Oracle automatically converts C2 to C1.
Example: Select nvl (null, '12') from dual;

6. nvl2 (C1, C2, C3) users may use nvl, but have you used nvl2? If C1 is not null, C2 is returned. If C1 is null, C3 is returned.
For example, select nvl2 ('A', 'B', 'C') isnull, nvl2 (null, 'B', 'C') isnotnull from dual;

7. sys_connect_by_path (COL, c) this function can only be applied to tree queries. Returns the path from the root to the node connected by C1. This function must be used together with the connect by clause.
For example:
Create Table tmp3 (
Rootcol varchar2 (10 ),
Nodecol varchar2 (10)
);

Insert into tmp3 values ('', 'a001 ');
Insert into tmp3 values ('', 'b001 ');
Insert into tmp3 values ('a001', 'a002 ');
Insert into tmp3 values ('a002 ', 'a004 ');
Insert into tmp3 values ('a001', 'a003 ');
Insert into tmp3 values ('a003 ', 'a005 ');
Insert into tmp3 values ('a005 ', 'a008 ');
Insert into tmp3 values ('b001', 'b003 ');
Insert into tmp3 values ('b003 ', 'b005 ');

Select lpad ('', level * 10, '=') | '>' | sys_connect_by_path (nodecol ,'/')
From tmp3
Start with rootcol = 'a001'
Connect by prior nodecol = rootcol;

8. sys_context (C1, C2 [, N]) truncates the value of the specified C2 parameter of the specified namespace C1 according to the specified length N and returns the result.
Oracle9i provides a built-in namespace userenv that describes the information of the current session. It has the following parameters:
Current_schema: name of the current mode;
CURRENT_USER: current user;
Ip_address: IP address of the current client;
OS _user: the operating system user of the current client;
And so on. For more detailed parameter columns, please refer to Oracle online documents.

Example: Select sys_context ('userenv', 'session _ user') from dual;
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. Abs (n) returns the absolute value of a number.
Example: Select ABS (-1000000.01) from dual;

2. Cos (n) returns the cosine of N.
Example: Select cos (-2) from dual;

3. ACOs (n) arccosine function, N between-1 and 1, return value between 0 and PI.
Example: Select ACOs (0.9) from dual;

4. bitand (N1, N2) bitwise AND operation. This is very interesting. Although I did not expect it to be used, I will explain it in detail:
Assume that 3 and 9 are bitwise AND operations. The binary form of 3 is 1001, And the binary form of 9 is 0001. The result is, and the number of decimal digits is 1.
Example: Select bitand (3, 9) from dual;

5. Ceil (n) returns the smallest integer greater than or equal to n.
Example: Select Ceil (18.2) from dual;
Test you and guess what the ceil (-18.2) value is.

6. Floor (n) returns the maximum integer less than or equal to n.
Example: Select floor (2.2) from dual;
Guess what the value of floor (-2.2) is.

7. bin_to_num (N1, N2,... n) binary conversion to decimal
For example: Select bin_to_num (1), bin_to_num (1, 0), bin_to_num (1, 1) from dual;

8. Sin (n) returns the positive and negative values of N, and N is a radian.
Example: Select sin (10) from dual;

9. sinh (n) returns the hyperbolic positive Xuan value of N, and N is a radian.
Example: Select sinh (10) from dual;

10. asin (n) antizheng Xuan function, N between-1 and 1, return value between PI/2 and-PI/2.
E. g.: Select asin (0.8) from dual;

11. Tan (n) returns the tangent of N, and N is a radian.
Example: Select Tan (0.8) from dual;

12. Tanh (n) returns the hyperbolic tangent of N, and N is a radian.
Example: Select Tanh (0.8) from dual;

13. atan (n) arctangent function. N indicates radians. Return Value: between PI/2 and-PI/2.
Example: Select atan (-444444.9999999) from dual;

14. exp (n) returns the N power of E, E = 2.71828183...
Example: Select exp (3) from dual;

15. ln (n) returns the natural logarithm of N, N> 0
Example: Select Ln (0.9) from dual;

16. Log (N1, N2) returns the base N2 logarithm of N1. N1> 0 and not 1, N2> 0
Example: Select log (1.1, 2.2) from dual;

17. Power (N1, N2) returns the N2 power of N1. N1 and N2 can be any number. However, if M is a negative number, N must be an integer.
Example: Select power (2.2, 2.2) from dual;

18. Sign (n) if n <0 returns-1, if n> 0 returns 1, if n = 0 returns 0.
Example: Select sign (14), sign (-14), sign (0) from dual;

19. SQRT (n) returns the square root of N, and N is a radian. N> = 0
Example: Select SQRT (0.1) from dual;

(2) character functions returning character values)
This type of function returns the same type as input.
The returned char value cannot exceed 2000 bytes;
The returned vchar2 value 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 length of the returned clob value 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. CHR (N [using nchar_cs]) returns the character corresponding to the specified value in the current character set.
Example: Select CHR (95) from dual;

2. Concat (C1, C2) connection string, equivalent to |
Example: Select Concat ('A', 'bb ') from dual;

3. initcap (c) converts the first letter of a word in a string to uppercase, and the other to lowercase.
Example: Select initcap ('What is this ') from dual;

4. nls_initcap (c) returns the specified string, converts the first letter in the string to uppercase, and other letters to lowercase.
Example: Select nls_initcap ('zhonghua minzhu') from dual;
It also has a parameter: nlsparam is used to specify the sorting rule, which can be ignored. By default, this parameter is the sorting rule of the current session.

(3) character functions returning number values)
This function supports all data types.
1. The use of ASCII (C) and CHR functions is just the opposite. This function returns the value of the specified character in the current character set.
Example: Select ASCII ('_') from dual;

(4). Date functions (datetime functions)
In this function, except months_between, all values return a date.
1. current_timestamp ([N]) returns the date and time of the time zone of the current session. N indicates millisecond-level precision, not greater than 6
Example: Select current_timestamp (3) from dual;

2. localtimestamp ([N]) returns the date and time of the time zone of the current session. N indicates millisecond-level precision, not greater than 6
Example: Select localtimestamp (3) from dual;

3. systimestamp ([N]) returns the date and time of the time zone of the current database. N indicates the precision in milliseconds,> 0 and <6
Example: Select distinct imestamp (4) from dual;

4. dbtimezone returns the current time zone of the database.
Example: Select dbtimezone from dual;

5. sessiontimezone: returns the time zone of the current session.
Example: Select sessiontimezone from dual;

6. Extract (key from date) Key = (year, month, day, hour, minute, second) indicates the specified date column from the specified time
Example: Select extract (year from sysdate) from dual;

7. to_timestamp (c1 [, FMT]) converts a specified character to timestamp.
Example: Select to_timestamp ('2017-8-22 ', 'yyyy-MM-DD hh: MI: ss') from dual;

(5). conversion functions (conversion functions)
A Conversion Function converts a specified character from one type to another. Generally, this type of function follows the following conventions: the function name follows the type to be converted and the output type.

1. bin_to_num (N1, N2. .. n) converts a set of bitvectors into an equivalent decimal form.
Example: Select bin_to_num (1, 1, 0) from dual;

2. Cast (C as newtype) converts a specified string to a specified type, which is only valid for character types, such as char, number, date, and rowid. This type of conversion has a special table that specifies which type can be converted to which type, which is not described here.
Example: Select cast ('200' as number (5) from dual;

3. chartorowid (c) converts a string to the rowid type.
Example: Select chartorowid ('a003d1abbefaabsaa0 ') from dual;

4. rowidtochar (rowid) converts rowid to the varchar2 type. The return string is 18 bytes in length.
Example: Select rowidtochar (rowid) from dual;

5. to_multi_byte (c) converts the specified character to a full-width character and returns a char string.
Example: Select to_multi_byte ('abc ABC Zhonghua ') from dual;

6. to_single_byte (c) converts the specified character to a halfwidth and returns a char string.
Example: Select to_single_byte ('abc ABC Zhonghua ') from dual;

(6) other auxiliary functions (Miscellaneous single-row functions)
1. coalesce (N1, N2,... n) returns the first non-null value in the sequence
Example: Select coalesce (null, 5, 6, null, 9) from dual;

2. Dump (exp [, FMT [, start [, length])
Dump is a very powerful function that is useful for those who have a deep understanding of Oracle storage. So those of us who are just applications do not know where to apply them. This section only describes usage and does not provide in-depth analysis on its functions.

As shown above, dump has many parameters. Essentially, the varchar2 value is returned in the internal representation of the exp of the specified length in a specified format. FMT can be in four formats: 8 | 10 | 16 | 17, which are hexadecimal, and single-character. The default value is decimal. The start parameter indicates the start position, and the length parameter indicates the number of strings separated by commas.
Example: Select dump ('abcdefg', 17,2, 4) from dual;

3. empty_blob and empty_clob functions both return the null lob type. They are usually used in insert and update statements to initialize the lob column or leave it empty. Empty indicates that lob has been initialized, but it has not been used to store data.

4. nls_charset_name (n) returns the character set name corresponding to the specified value.
Example: Select nls_charset_name (1) from dual;

5. nls_charset_id (c) returns the Character Set ID corresponding to the specified character set.
Example: Select nls_charset_id ('us7ascii ') from dual;

6. nls_charset_decl_len (N1, N2) returns the declared width (in characters) of an nchar value. N1 is the length of the value in bytes and N2 is the character set ID of the value.
Example: Select nls_charset_decl_len (100, nls_charset_id ('us7ascii ') from dual;

7. sys_extract_utc (timestamp) returns the standard universal time, that is, Greenwich Mean Time.
Example: Select sys_extract_utc (current_timestamp) from dual;

8. sys_typeid (object_type) returns the ID of the object type.
For example, I have not created a custom object. What is the example?

9. uid returns an integer that uniquely identifies the current database user.
Example: Select uid from dual;

10. The user returns the current session user.
Example: Select User from dual;

11. userenv (c) This function is used to return information about the current Session. According to the Oracle documentation, userenv is a legacy function to maintain backward compatibility. Oracle recommends that you use the sys_context function to call the userenv namespace to obtain relevant information.
Example: Select userenv ('language') from dual;

12. vsize (c) returns the number of bytes of C.
Example: Select vsize ('abc Zhonghua ') from dual;