Oracle date functions/character functions/numeric functions/conversion functions/Aggregate functions

Oracle date functions/character functions/numeric functions/conversion functions/Aggregate functions Oracle date functions: [SQL] MONTHS_BETWEEN: returns the difference of the month between two dates MONTHS_BETWEEN ('01-EP-95 ', '11-JAN-94 ') ==> 19.6774194 www.2cto.com ADD_MONTHS: add the number of copies to the date. ADD_MONTHS ('11-JAN-94 ', 6) ==> '11-JUL-94' NEXT_DAY: Specifies the day after the date. NEXT_DAY ('01-SEP-95 ', 'Friday') ==> '08-SEP-95' LAST_DAY: the last day of the month. LAST_DAY ('01-SEP-95) ==> '30-SEP-95 'ROUND: Rounding the date www.2cto.com ROUND ('25-JUL-95', 'month') ==> 01-AUG-95 ROUND ('25-JUL-95 ', 'Year') ==> 01-JAN-96 TRUNC: Truncated date TRUNC ('25-JUL-95', 'month ') ==> 01-JUL-95 TRUNC ('25-JUL-95 ', 'Year') ==> 01-JAN-95 what are Oracle date functions: YYYY ---- represents the full YEAR ---- year mm ------ double digit MONTH --- full name of the month dy ------ three characters DAY in the middle of each week ----- full name of the DAY Oracle character function: [SQL] 1. LOWER (string) converts the input string to lowercase select lower ('hello') from dual; www.2cto.com 2. UPPER (string) converts the input string into a large write select upper ('hello') from dual; 3. INITCAP (str (Ing) converts the first letter of the input string into uppercase letters. PS: if not two letters are connected together, they are considered as new words, for example, a_ B a and B a B, both a and B will be converted into big write select initcap ('hello') from dual; 4. connector (|) to connect two strings with |. In addition, you can use the Concat function to connect strings. Example: select CONCAT (City, country) from Table select concat ('hello', 'World') from dual; 5. the LPAD and RPAD fill functions are two very similar functions in the same way. You can add a group of characters to the left [right] side of a column. Example: RPAD (city, 20 ,'. '). If the ciy value is not smaller than the length of 20, use '. 'fill in on the right and fill in 20. If it is filled with spaces, it looks like the left alignment. 6. LTRIM, RTRIM, and TRIM are used to delete unnecessary characters from the left, right, and left and right sides of the string. spaces are deleted by default. Select ltrim ('helloworld', 'Hello') from dual; select rtrim ('helloworld', 'World') from dual; 7. the LENGTH function is very simple, that is, to tell the user how long a string is, that is, the number of characters in the string, the function itself is not very useful, but can be used as a part of other functions, it is used to calculate the number of spaces required for a table, or as part of the orderby clause. PS: You cannot use functions such as LENGTH for a LONG data column. 8. SUBSTR (string, start [, count]) This function tells oracle to extract a subset of string, start position, count extraction length, when count is not specified, extracted to the end of the string by default. You can also specify start as a negative number. If it is a positive number, it is calculated from the starting position of the string (left side). If it is a negative number, it is calculated from the end position (right side ). PS: negative numbers cannot be used in the Char data type. Because Char is of a fixed length, spaces are used to fill their values until they are extended to the full length of the column. Select substr ('helloworld', 3,5) from dual; www.2cto.com 9. INSTR returns the position of the specified string INSTR (string, set [, start [, occurrence]). If start is specified, oracle skips all the previous strings to start searching. occurence, it forces the instr to skip the first few times to match the string and give the next matching position. If occurence specifies 3, it is the third matching position. For example, instr ('abacaaa', 'A', 2nd) matches the string A from ABACAAA, starts from two locations, and matches the position of A for times. PS: If the set contains more than one character but a few characters, INSTR gives the first character position in the character set. Select instr ('World', 'L') from dual; 10. translate (): select translate ('helloworld', 'O', 'A') from dual; 11. replace (): replace the string select replace ('helloworld', 'World', 'continued class') from dual; www.2cto.com Oracle numeric functions: [SQL] 1.1, ABS (n) function Description: returns the absolute value of value n. Examples: select abs (-15) "test" from dual; 1.2, ACOS (n) Function Description: returns the arc cosine of the value n. The input value ranges from-1 ~ 1. the return value is radians. Examples: select acos (0.6) "test", acos (-. 6) "test1" from dual; 1.3, COS (n) Function Description: return the cosine of the value n. The return value is radians. Examples: select cos (1.6) "test", cos (-6) "test1" from dual; 1.4, SIN (n) Function Description: return the sine value of the value n. Examples: select sin (1.6) "test", sin (-6) "test1" from dual; 1.5, ASIN (n) Function Description: returns the arc sine of the value n. The input value ranges from-1 ~ 1. the return value is radians. Examples: select asin (0.6) "test", asin (-0.6) "test1" from dual; 1.6, TAN (n) Function Description: returns the tangent of the value n. Examples: select tan (6) "test", tan (-0.6) "test1" from dual; 1.7, ATAN (n) function www.2cto.com Description: returns the arc tangent value of the value n. The input value is arbitrary and the return value is radians. Examples: select atan (6) "test", atan (-0.6) "test1" from dual; 1.8, ATAN2 (n, m) Function Description: returns the arc tangent of n/m. The input value is arbitrary and the return value is radians. Examples: select atan2 (0.9) "test", atan2 (-9,-1.9) "test1" from dual;, SINH (n) Function Description: returns the hyperbolic sine of n. Any input value. Examples: select sinh (6) "test", sinh (-0.6) "test1" from dual; 1.10, TANH (n) Function Description: returns the hyperbolic tangent of the value n. Any input value. Examples: select tanh (6) "test", tanh (-0.6) "test1" from dual; 1.11, CEIL (n) Function Description: returns the smallest integer greater than or equal to the value n. Www.2cto.com Examples: select ceil (6) "test", ceil (6.6) "test1" from dual; 1.12, COSH (n) Function Description: return the hyperbolic cosine value of the value n. Examples: select cosh (6) "test", cosh (6.6) "test1" from dual; 1.13, EXP (n) Function Description: Return n times of e. (E = 2.71828183 ...) Examples: select exp (6) "test" from dual; 1.14, FLOOR (n) Function Description: returns the maximum integer less than or equal to the value n. Examples: select floor (6) "test", floor (9.3) "test1" from dual; 1.15, LN (n) Function Description: returns the natural logarithm of the value n. (N must be greater than 0) Examples: select ln (6) "test" from dual; 1.16, LOG (m, n) Function Description: returns the logarithm of the base-m value n. (M> 1, n> 0) Examples: select log (6, 3) "test" from dual; 1.17, MOD (m, n) function www.2cto.com description: returns the remainder after m/n. If n = 0, returns m (modulo operation) Examples: select mod (6, 3) "test" from dual; 1.18, POWER (m, n) Function Description: returns n Ming Examples of m: select power (6, 3) "test" from dual; 1.19, ROUND (n, [m]) Function Description: execute the rounding operation. m can be omitted. When m is omitted, It is rounded to the integer. When m is positive, it is rounded to the m digit after the decimal point. When m is negative, rounding to the first m digit of the decimal point. Examples: select round (6.698, 2) "test" from dual; 1.20, SIGN (n) Function Description: Checks positive and negative values. If n <0, return-1; if n> 0, 1 is returned. If n = 0, 0 is returned. Examples: select sign (6.698) "test", sign (-9) "test1", sign (0) "test2" from dual; 1.21, SQRT (n) Function Description: returns the square root of n. (N> = 0) Examples: select sqrt (6.698) "test" from dual; 1.22, TRUNC (n, [m]) Function Description: truncates the value n, m can be omitted, if m is omitted, the fractional part of n is truncated. If m is positive, the n is truncated to the m digit after the decimal point; when m is a negative number, n is truncated to the m-digit Examples before the decimal point: select trunc (6.698, 2) "test", trunk (696.3,-2) "test1" from dual; www.2cto.com Oracle Conversion Function: [SQL] TO_CHAR converts a date or number to a string TO_DATE. in the database, the date type conversion function TO_NUMBER converts a character to a number -- extract (): query the current date select extract (year from sysdate) | 'Year' | extract (month from sysdate) | 'month' | extract (day from sysdate) | 'day' as converted date from dual -- to_char (): date conversion format, default "DD-MON-YY" select to_char (sysdate) from dual; select to_char (sysdate, 'yyyy "year" MM "month" DD "day" HH ":" MI ":" ss') from dual; -- 12-hour format select to_char (sysdate, 'yyyy "-" MM "-" DD "-" HH24 ":" MI ":" SS ") from dual; -- 24-hour format: www.2cto.com -- decode (): it is equivalent to multiple if-else (taking the EMP table as an example) select empno as number, ename as name, decode (deptno, 10, 'board', 20, 'authorization', 30, 'development departments', 'monstro') as department from emp Oracle aggregate function: [SQL] Count returns the number of records found Min returns the maximum value of a numeric column or computed column Max returns the maximum value of a numeric column or computed column Sum returns a numeric column or calculated column Sum avg returns average value of a numeric column or computed Column