Oracle Database built-in functions: Numeric functions, character functions, date functions, conversion functions and Their Application in query statements

Oracle Database built-in functions: Numeric functions, character functions, date functions, conversion functions and Their Application in query statements

Numeric functions: 1. Rounding the function round () from dual: a row and a column form select round (23.4) from dual; -- by default, m is 0 select round (23.45, 1) from dual; -- 1 indicates that the last decimal point is retained, so the second decimal point is rounded to select round (23.45,-1) from dual; --- 1 indicates rounding the last decimal point to an integer, the first digit is 3 rounded to 202, the entire function is used: select ceil (23.45), floor (23.45) from dual; 3. Common calculation: abs (n) absolute value function: select abs (23.45), abs (-23), abs (0) from dual; mod (m, n): returns the remainder function. If either m or n has a null value, the return value is null. select mod (5, 2) from dual; select mod (5, null) from dual; power (m, n ): returns m's n power select power (2, 3), power (null, 2) from dual; sqrt (n): returns the square root function select sqrt (16) from dual; 4. trigonometric function: sin (n), asin (n): n stands for radian select sin (3.14) from dual; cos (n), acos (n); character function: 1. case-sensitive conversion functions: upper (char), lower (char), and initcap (char). Convert the first letter into uppercase select upper ('abcde'), lower ('ade '), initcap ('asd ') from dual; 2. Obtain the substring function: substr (char, [m [n]), if n is omitted, it indicates that m is truncated from m to the end of the string. m is 0, which indicates that m is truncated from the first letter of the string. m is negative. select substr ('abcde ', 2, 3); substr ('abcde', 2), substr ('abcde',-2, 1) from dual; 3. Function for obtaining String length: length (char) select length ('abc') from dual; 4. String concatenation function: concat (char1, char2); similar to | Operator, select concat ('AB ', 'cd') from dual; select 'AB' | 'cd' from dual; 5. Remove the substring function: trim (c2 from c1), c2 is a character, c1 is the string select trim ('c' from 'textac') from dual; -- dcce, remove the character select trim ('c' from 'cdd') whose left and right sides are the beginning of c ') from dual; -- ddltrim (c1 [, c2]); c1 is a string, c2 is a character, remove a character from the header rtrim (c1 [, c2]): trim (c1 ): ltrim (c1): if there is only one parameter, the space on the left is removed. rtrim (c1 ): if there is only one parameter, remove the space on the right. 7. replace () select replace ('abcde', 'A', 'A') from dualselect replace ('abcde ', 'A') from dual; -- replace with space select replace ('abcde', 'AB', a) from dual; Date function: system time function: SYSDATE default format DD-MM-YY (day-month-year) ADD_MONTHS (date, I): select add_months (sysdate, 3), add_months (sysdate,-3) from dual; NEXT_DAY (date, char ): select next_day (sysdate, 'monday') from dual; LAST_DAY (char): select last_day (sysdate) from dual; MONTHS_BETWEEN (char1, char2 ): select months_between ('20-January-15', '10-January-15') from dual; Date operation function: obtain the year, month, date, and hour. extract the specified date from the select extract (year from sysdate) from dual; select extract (month from sysdate) from dual; select extract (day from sysdate) from dual; select extract (hour from timestamp '2017-10-1 17:22:22 ') from dual; Conversion Function: TO_CHAR (date [, fmt [, params]): date to be converted, fmt: conversion format, params: the default date language is DD-MM-YYselect to_char (sysdate, 'yyyy-MM-DD HH24: MI: ss') from dual; // by default, the function TO_DATE (char [, fmt [, params]) for converting the last parameter to a date without writing a character: select to_date ('2017-05-22 ', 'yyyy-MM-DD ') from dual; // note: the function of converting to_date () to a character by displaying date numbers in the default format select to_char (12345.678,' $99,999.999 ') from dual; function TO_NUMBER (char [parms]) for converting character to number; select to_number ('$1,000', '$9999') from dual; when a function is used in a query statement, the special changes in the output results will not affect the data in the database to be queried in the employee information table. select substr (cardid, 7,8) from users; replace all Department No. 01 with information technology select replace (deptno, '01', 'info') from users; obtain the age field from users from the employee information table and the 10 plus select mod (age, 10) from users; the select extract (year from regdate) from users for the employee's employment year; query the information of employees who joined the company in March. select * from users where extract (month from regdate) = 5;