Numerical functions, character functions, date functions, conversion functions and their application in query statements of Oracle database built-in functions

Numeric functions: 1, rounding function round () from dual: A row of a column composed of select round (23.4) from dual;--default not write M for M for 0select round (23.45,1) from dual;-- 1 means to keep one digit after the decimal point, then the second digit of the decimal point rounding Select Round (23.45,-1) from dual;---1 means rounding the rounding before the decimal point, the previous one is 3 rounded to 202, the rounding function: 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): Take the remainder function, If there is a value of NULL in M and n, the result returns a null value of select mod (5,2) from Dual;select mod (5,null) from Dual;power (M,n): Represents the N power of the returned m select power ( 2, 3), Power (null,2) from DUAL;SQRT (n): function for square root select sqrt from dual;4, trigonometric function: Sin (n), ASIN (n): N for radians select sin (3.14) From Dual;cos (n), ACOs (n), character function: 1, Case conversion function: Upper (char), Lower (char), Initcap (char), capitalize the first letter select Upper (' ABCDE '), Lower (' ADe '), Initcap (' ASD ') from dual;2, get substring function: substr (Char,[m[n]]), N if the ellipsis represents from M to the end of the string, M is 0 for the first letter of the string to intercept m as a negative number means to intercept select substr (' ABCDE ', 2,3) from the tail of the string, substr (' ABCDE ', 2), substr (' ABCDE ') , -2,1) from dual;3, gets the function of string length: Length (char) Select Length (' abc ') from Dual;4, string join function: concat (CHAR1,CHAR2); The function of the operator is the same as select Concat (' AB ', ' CD ') from Dual;selecT ' ab ' | | ' CD ' from dual;5, remove substring function: Trim (C2 from C1), C2 is a character, C1 is the string select trim (' C ' from ' CDCCEC ') from Dual;--dcce, Removing the left and right 2 sides is the C-starting character of select trim (' C ' from ' CCDD ') from Dual;--ddltrim (C1[,C2]); C1 is a string, C2 is a character, removing one character from the head RTrim (C1[,C2]): Trim (c1 ): Represents the removal of the left and right 2 sides of the Space LTrim (C1): Only one parameter means to remove the left side of the space RTrim (C1): Only one parameter means to remove 7, replace the function replacement () Select replace (' ABCDE ', ' a ', '  A ') from Dualselect replace (' ABCDE ', ' a ') the from dual;--is replaced by a space select replace (' ABCDE ', ' AB ', a) from dual; Date function: System time function: Sysdate Default format Dd-mm-yy (Sun Moon 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): Calculates the month of the interval between 2 dates select Months_between (' 20月-May-15 ', ' October-January -15 ') from dual; date manipulation function: Get year, month, date  , hours extract the function of the specified date part 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 ' 2015-10-1 17:22:22 ') from dual; conversion function: Date converted toCharacter function To_char (Date[,fmt[,params]]):d ate: The date to convert, FMT: Format of conversion, params: The language of the date defaults to Dd-mm-yyselect to_char (Sysdate, ' Yyyy-mm-dd HH24:MI:SS ') from dual;//the last parameter does not convert the character to the date function to_date (Char[,fmt[,params]]): Select To_date (' 2015-05-22 ', ' Yyyy-mm-dd ') from dual;//Note: to_date () The function of converting a date number to a character according to the default format of the system select TO_CHAR (12345.678, ' $99,999.999 ') from DUAL; Character conversion to a number function To_number (char[parms]); Select To_number (' $1,000 ', ' $9999 ') from dual; Special changes in output results when using functions in query statements do not affect the values in the database the employee's birthday select substr (cardid,7,8) from users is queried in the Employee Information table, and the department number 01 is replaced by the information technology Select Replace ( Deptno, ' 01 ', ' information technology ') from users, the age field in the Employee Information table and 10, select mod (age,10) from users, and the year of the employee entry select Extract RegDate) from users, query the employee information for the May entry select * from the users where extract (month from regdate) = 5;

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Numerical functions, character functions, date functions, conversion functions and their application in query statements of Oracle database built-in functions