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

Source: Internet
Author: User
Tags acos asin natural logarithm ranges rtrim truncated

Oracle date functions:

Months_between: returns the difference of the month between two dates months_between ('01-EP-95 ', '11-Jan-94') ==> 19.6774194 add_months: Number of copies added 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 date 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 which Oracle date functions include: yyyy ---- represents the full year ---- year mm ------ double digit month --- full name of the month dy ------ full name of the three characters day in the middle of each week ----- day

Oracle character functions:

1. lower (string) converts the input string to lowercase select lower ('hello') from dual; 2. upper (string) converts the input string into a large write select upper ('hello') from dual; 3. initcap (string) 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 tableselect 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; 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 string select Replace ('helloworld', 'World', 'continued class') from dual;

Oracle numeric functions:

1.1. Abs (n) Function Description: returns the absolute value of 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 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. Examples: Select Ceil (6) "test", Ceil (6.6) "test1" from dual; 1.12, cosh (n) Function Description: returns the hyperbolic cosine 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 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;

Oracle conversion functions:

To_char converts a date or number to a string. to_date converts a string to a date type conversion function in the database. to_number converts the 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 -- 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 functions:

Count returns the number of records found Min returns the maximum value of a numeric column or calculated column Max returns the maximum value of a numeric column or calculated column sum returns a numeric column or calculated column sum AVG returns a numeric Column or calculate the average value of a column.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.