Oracle Common functions

The character function is the most commonly used function in Oracle, so let's see what the character functions are: lower (Char): Converts the string to lowercase format. Upper (Char): Converts a string to a uppercase format. Length (Char): Returns the length of the string. SUBSTR (Char, M, N): intercepts a substring of a string, n means n characters, not a delegate to nth replace (CHAR1, search_string, replace_string) InStr (C1,C2,I,J)-determines whether a character or string exists, returns the index where the occurrence occurred, otherwise returns less than 1, searches for the specified character in a string, and returns the location of the specified character; C1 the searched string C2 The start of the search for the string I want to search, defaults to the location where 1J appears, and defaults to 1: Displays all employees ' names in lowercase. sql>SelectLower (ename) fromEMP; Issue: Displays all employees ' names in uppercase. SQL>SelectUpper (ename) fromemp; problem: Displays the name of an employee who is exactly 5 characters. SQL>Select* fromEmpwhereLength (ename) =5; problem: Displays the first three characters of all employee names. SQL>SelectSUBSTR (ename,1,3) fromEMP: The name of all employees is displayed in uppercase letters, followed by lowercase. SQL>SelectUpper (substr (ename,1,1)) || Lower (substr (ename,2, Length (ename)-1)) fromEMP; Issue: Displays the names of all employees in lowercase letters, followed by uppercase. SQL>SelectLower (substr (ename,1,1)) || Upper (substr (ename,2, Length (ename)-1)) fromEMP; issue: Show all employees ' names, replace All "A" SQL with "I am Tiger">SelectReplace (ename,'A','I'm a tiger .') fromEMP; Issue: InStr (Char1,char2,[,n[,m]]) usage SQL>SelectInStr'AZHANGSANBCD','Zhangsan') fromDual --return 2SQL>SelectInStr'Oracle traning','RA',1,1) instring fromDual --return 2SQL>SelectInStr'Oracle traning','RA',1,2) instring fromDual --return 9SQL>SelectInStr'Oracle traning','RA',1,3) instring fromDual --return 0, according to the conditions, due to RA only two times, the fourth parameter 3, that is, the 3rd occurrence of the position of RA, it is clear that the 3rd time is no longer appear, so the result returned 0. Note A space is also counted as a character SQL>SelectInStr'ABC','D') fromDual --Return 2, the mathematical function of the mathematical function of the input parameters and the return value of the data type are numeric types. Mathematical functions include COS,COSH,EXP,LN, log,sin,sinh,sqrt,tan,tanh,acos,asin,atan,round, etc. we speak most commonly: round (n,[m]) The function is used to perform rounding, and if M is omitted, is rounded to an integer. If M is a positive number, it is rounded to the M-bit of the decimal point. If M is a negative number, it is rounded to the M-bit of the decimal point. eg, SELECT round (23.75123) from dual; --returns 24SELECT round (23.75123, -1) from dual; --returns 20SELECT round (27.75123, -1) from dual; --returns 30SELECT round (23.75123, -3) from dual; --returns 0SELECT round (23.75123,1) from dual; --Returns 23.8SELECT Round (23.75123,2) from dual; --Returns 23. theSELECT Round (23.75123,3) from dual; --Returns 23.751trunc (n,[m]) This function is used to intercept numbers. If M is omitted, the fractional portion is truncated and if M is a positive number, the M-bit of the decimal point is truncated, and if M is a negative number, the first m bit of the decimal point is intercepted. eg, SELECT trunc (23.75123) from dual; --returns 23SELECT trunc (23.75123, -1) from dual; --returns 20SELECT trunc (27.75123, -1) from dual; --returns 20SELECT trunc (23.75123, -3) from dual; --returns 0SELECT trunc (23.75123,1) from dual; --Returns 23.7SELECT trunc (23.75123,2) from dual; --Returns 23. theSELECT trunc (23.75123,3) from dual; --Returns 23.751MoD (m,n) take the remainder function eg,SelectMoDTen,2) fromDual --return to 0SELECT MOD (Ten,3) from dual; --returns 1floor (n) returns the largest integer less than or equal to n ceil (n) returns the smallest integer greater than or equal to n eg, SELECT ceil (24.56) fromDual --return to 25SELECT floor (24.56) fromDual --returns 24ABS (n) returns the absolute value of the number n the processing of numbers, the most used in financial systems or banking systems, different processing methods, and different results for financial statements. Date function Date function is used to process data of date type. By default, the date format is DD-mon-yy is " A-7Months A"(1) Sysdate return to system time eg, SQL>SelectSysdate fromdual; (2The Oracle add_months function Oracle Add_months (time,months) function can get a time before or after n months for eg,SelectAdd_months (sysdate,-6) fromDual --The result of the query is the current time of half a year agoSelectAdd_months (Sysdate,6) fromDual --The result of the query is the time after the current time of six months (3) Last_day (d): Returns the last day of the month of the specified date question: Find an employee who has been in employment for more than 8 months. SQL>Select* fromEmpwhereSysdate>=add_months (HireDate,8question: Displays the name and date of employment of the employee who has been in service for 10 years. SQL>SelectENAME, HireDate fromEmpwhereSysdate>=add_months (HireDate, A*Tenquestions: For each employee, show the number of days that they joined the company. SQL>SelectFloor (Sysdate-hiredate)"number of days in the job", ename fromemp, or SQL>SelectTrunc (Sysdate-hiredate)"number of days in the job", ename fromemp; problem: Find all employees employed on the 3rd day of the month. SQL>SelectHiredate,ename fromEmpwhereLast_day (HireDate)-2=HireDate; Conversion function conversion functions are used to convert data types from one type to another. In some cases, the data type of the Oracle server allow value is different from the actual, when Oracle server implicitly converts data types such as: CREATE TABLE T1 (IDint) insert into T1 values ('Ten');--so that Oracle will automatically'Ten'-TenCREATE TABLE t2 (ID varchar2 (Ten) ; insert into T2 values (1); -So Oracle will automatically 1--and'1'What we want to say is that while Oracle can do the implicit conversion of the data type, it does not adapt to all situations, and in order to improve the reliability of the program, we should use the conversion function. To_char () function You can use select Ename, HireDate, Sal fromEmpwhereDeptno =Ten, but, in some cases, this does not meet your needs. Question: Whether the date can be displayedScore ofsec SQL>Selectename, To_char (HireDate,'YYYY-MM-DD Hh24:mi:ss') fromemp; question: Whether the salary can display the specified currency symbol for SQL>yy: Two-digit year2004-Genevayyyy: Four-digit year 2004 mm: two-digit month8Month-- ,DD: Two-digit day --- -Hh24:8 Point- -Hh12:8 Point- ,mi, SS-show minutes \ seconds9: Displays the number and ignores the previous 00: Displays a number, if the number of digits is insufficient, then 0.: Display decimal point at specified position: Comma $: In the specified position, plus USD L: Precede the number with the local currency symbol C: Precede the number with the international currency symbol G: Displays the group separator at the specified position, D: Displays the decimal symbol at the specified position ( .) Question: When displaying a salary, add the local currency unit to the preceding SQL>Selectename, To_char (HireDate,'YYYY-MM-DD Hh24:mi:ss'), To_char (Sal,'L99999.99') fromEMP; issue: Show all employee SQL for 1980 entry>Select* fromEmpwhereTo_char (HireDate,'yyyy')=1980; problem: Show all employee SQL for December entry>Select* fromEmpwhereTo_char (HireDate,'mm')= A; The to_date () function function to_date is used to convert a string into a date type of data. Question: Can I add a date in the way the Chinese are accustomed to the year-month-day. eg, SELECT to_date ('2012-02-18 09:25:30','YYYY-MM-DD Hh24:mi:ss') from dual; V. Sys_context () system functions1Terminal: The identifier of the terminal that the current session client corresponds to, such as the computer name2) Language: Language3) db_name: Current database name4Nls_date_format: The date format that the current session customer corresponds to5) Session_user: The database user name for the current session client6) Current_schema: The default scheme name for the current session customer7) Host: Returns the name of the host where the database is located through this function, you can query some important information, such as which database you are using? SelectSys_context ('USERENV','db_name') fromDual, note: Userenv is fixed, cannot be changed, db_name can be replaced by other, eg,SelectSys_context ('USERENV','language') fromdual;SelectSys_context ('USERENV','Current_schema') fromDual

Oracle Common functions