Oracle Common functions

the character function character function is the most commonly used function in Oracle, let's see what the character functions are: lower (char): Convert the string to lowercase. Upper (char): Converts a string to an uppercase format. Length (char): Returns the lengths of the strings. substr (char, M, N): intercepts a substring of a string, n represents the meaning of n characters, not a delegate fetch to nth replace (char1, search_string, replace_string) InStr (C1 , c2,i,j)--Determines whether a character or string exists, returns the index of where it appears, 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 starting position of the search for the string I want to search, default to 1 J where it appears, default to 1

Issue: Display the names of all employees in lowercase sql> select lower (ename) from EMP; Issue: Display the names of all employees in uppercase. Sql> Select Upper (ename) from EMP; Issue: Displays the name of an employee who is exactly 5 characters. Sql> SELECT * from emp where length (ename) = 5; Issue: Displays the first three characters of all employee names. Sql> Select substr (ename, 1, 3) from EMP; Issue: The names of all employees are displayed in uppercase letters and lowercase in the following way. Sql> Select Upper (substr (ename,1,1)) | | Lower (substr (Ename,2,length (ename)-1)) from EMP; Issue: Displays the names of all employees in lowercase with the first letter, followed by uppercase. Sql> Select Lower (substr (ename,1,1)) | | Upper (Substr (Ename,2,length (ename)-1)) from EMP; Issue: Show all employee names, replace All "a" sql> Select Replace with "I am Tiger" (ename, ' a ', ' I am a tiger ') from EMP; Question: InStr (Char1,char2,[,n[,m]]) usage sql> Select InStr (' Azhangsanbcd ', ' Zhangsan ') from dual; --Return 2 sql> Select InStr (' Oracle traning ', ' Ra ', 1, 1) instring from dual; --Return 2 sql> Select InStr (' Oracle traning ', ' Ra ', 1, 2) instring from dual; --Return 9 sql> Select InStr (' Oracle traning ', ' Ra ', 1, 3) instring from dual; --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 that spaces are also counted as a character sql> select InStr (' abc ', ' d ')From dual; --return 0

second, mathematical functionsThe input parameters and the data type of the return value of the mathematical function are numeric types. Mathematical functions include COS,COSH,EXP,LN, log,sin,sinh,sqrt,tan,tanh,acos,asin,atan,round, etc. we speak the most commonly used:Round (n,[m]) The function is used to perform rounding, and if M is omitted, 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; --Return to the 1 SELECT round (23.75123,-) from dual; --Return to the 1 SELECT round (27.75123,-) from dual; --Return to the 3 SELECT round (23.75123,-) from dual; --Return 0 SELECT round (23.75123, 1) from dual; --Return 23.8 SELECT round (23.75123, 2) from dual; --Return 23.75 SELECT round (23.75123, 3) from dual; --Return 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; --Return to Trunc SELECT (23.75123,-1) from dual; --Return to the 1 SELECT trunc (27.75123,-) from dual; --Return to the 3 SELECT trunc (23.75123,-) from dual; --Return 0 SELECT trunc (23.75123, 1) from dual; --Return 23.7 SELECT trunc (23.75123, 2) from dual; --Return 23.75 SELECT trunc (23.75123, 3) from dual; --Return 23.751 mod (m,n) take the remainder function eg, select mod (10,2) from dual;--return 0 Select mod (10,3) from dual;--return 1 floor (n) returns less than or equal to n The maximum integer ceil (n) returns the smallest integer greater than or equal to n eg, select Ceil (24.56) from dual;--Return to SELECT floor (24.56) from dual;--return ABS (n) The absolute value of the number n is used in the financial system or the banking system, the most, the different processing methods, the financial statements have different results

third, date functionDate functions are used to process data of the date type. By default the date format is dd-mon-yy that is "December-July-12" (1)Sysdate return system time eg, sql> select sysdate from dual; (2) OracleThe Add_months function Oracle Add_months (time,months) function can get the time of the n months before or after a certain time eg, select Add_months (sysdate,-6) from dual; --the result of the query is the time before the current time of the year Select Add_months (sysdate,6) from dual; --the result of the query is half a year after the current time (3)last_day (d): Returns the last day of the month of the specified date question: Find employees who have been in the workforce for more than 8 months sql> select * from emp where sysdate> =add_months (hiredate,8); Question: Displays the name and date of employment of the employee who has been in service for 10 years. Sql> Select ename, hiredate from EMP where sysdate>=add_months (HIREDATE,12*10); Issue: For each employee, show the number of days that they joined the company. Sql> Select Floor (sysdate-hiredate) "Entry Days", ename from EMP; or sql> Select Trunc (sysdate-hiredate) "Entry Days", ename from EMP; Question: Find out all employees employed on the 3rd day of the month. Sql> Select Hiredate,ename from emp where Last_day (hiredate) -2=hiredate;            the transform function conversion function is 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 the type of conversion data such as: CREATE TABLE T1 (id int); INSERT into T1 values (' 10 ');--so that Oracle automatically-->10 CREATE TABLE t2 (ID varchar2 (10)); INSERT into T2 values (1); -So Oracle will automatically 1--' 1 '; what we're saying is that while Oracle can do the implicit conversion of the data type, it doesn't fit all, and to improve the reliability of the program, we should use a conversion function.

To_char () function You can use select Ename, HireDate, Sal from emp where deptno = 10; Display information, however, in some cases, this does not meet your needs. Question: Whether the date can be displayed when/minutes/sec sql> Select Ename, To_char (hiredate, ' Yyyy-mm-dd hh24:mi:ss ') from EMP; Question: Whether the salary can display the specified currency symbol sql>YY: two-digit year 2004-->04 YYYY: four-digit year 2004 MM: Two-digit month August-->08 DD: two-digit day 30th-->30 hh24:8 Point-- >20 hh12:8 point-->08 9: Displays the number and ignores the previous 0 0: Displays the number, if the number of digits is not enough, then 0 to complement .: Show decimal point at specified location : Displays comma $: Add USD l: Add local currency symbol c: Precede the number with the international currency symbol g: Displays the group separator, d: Displays the decimal symbol (.) in the specified position

Question: When displaying a salary, add the local currency unit to the front sql> select ename, To_char (hiredate, ' yyyy-mm-dd hh24:mi:ss '), To_char (Sal, ' L99999.99 ') from Emp Issue: Show all employees in 1980 sql> select * from emp where TO_CHAR (hiredate, ' yyyy ') = 1980; Issue: Show all December entry staff sql> SELECT * from emp where TO_CHAR (hiredate, ' mm ') = 12;

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 function 1) Terminal: The identifier of the terminal for the current session client, such as computer name 2) Language: Language 3) db_name: Current database name 4) Nls_date_format: Date format for the current session customer 5) Session_user: The database user name for the current session client 6) Current_schema: The default scheme name for the current session client 7) Host: Returns the name of the host that the database resides in through the function, Can you query some important information, such as which database you are using? Select Sys_context (' USERENV ', ' db_name ') from dual; Note: USERENV is fixed, cannot be changed, db_name can be replaced by other, eg, select Sys_context (' USERENV ', ' language ') from dual; select Sys_context (' USERENV ', ' Current_schema ') from dual;

Oracle Common functions