In the database in order to facilitate user development, often provide a series of support functions, the use of these functions can be used for data processing. One, String functions String functions can be processed for strings in the following ways: UPPER (), LOWER () ', Initcap (), REPLACE (), LENGTH (), SUBSTR (). 1, Case conversion functions 1, to uppercase functions: String upper (Column | string)  2, turn lowercase functions: string lower (Column | string) Example: Change an employee name in an EMP table all lowercase (uppercase) select LOWER (ename) from emp; --to lowercase SELECT U Pper (ename) from emp; --to uppercase 2, initial capitalization function Syntax: Initcap (column | String) Example: capitalize the initials of an employee in an EMP table: select Initcap (ename) from E mp; 3, calculating the length of the string Example: Querying the name of each employee and the length of the employee's name select ename, Length (ename) from emp; 4, string substitution &nbs p; syntax is: characterString Replace (column | data, what to look for, new content) Example: Replace the letter A in all employees ' names with "_" select REPLACE (ename, UPPER (' a '), ' _ ') from emp; Example: Using the REPLACE () function to eliminate whitespace data in a string select REPLACE (' Hello World Nihao zaijian ', ' ', ') & nbsp; from dual; --dual for virtual table 5, string interception syntax One: string substr (column | data, start point), truncated from the specified start point to the end; Syntax two: string substr (column | data, start point, length): Intercepts the substring of the specified range. Note: The number of SUBSTR () function bits is calculated starting from 1 (subscript is starting with 1) Example: Intercept from the specified position to the end select SUBSTR (' Helloworldnihao ', one-to-one) from dual; ---from 11th to last Example: Capturing part of a specified range of data SELECT SUBSTR (' Helloworldnihao ', 6,5) from dual; --- Starting from the sixth position, the totalIntercept 5-bit Two, numeric functions Numeric functions are processed for numbers: ROUND (), TRUNC (), MOD (). 1, rounding function Syntax: ROUND (column | number, "Keep decimal place") & nbsp; NOTE: If you do not set the reserved decimal places, you do not retain decimals, rounding Examples: select ROUND (2.33333, 2), ROUND (2.33333), ROUND (2.777,2) from dual;& nbsp //answer:2.33 ,2 ,2.78 2, intercept decimals, All decimals are not rounded syntax: Numeric TRUNC (column | number, [decimal place]). Example: SELECT TRUNC (2.3333,2), TRUNC (2.77777,2) from dual; //answer:2.33 ,2.77 3, modulo (remainder) Syntax: MOD (column 1| number 1, column 2| number 2) Example: SELECT MOD (10,3) from dual,----10/3 remainder Three, date function & nbsp 1, get system time: select sysdate from dual;----output Current time date SELECT systimestamp from dual;---Output current time includes minutes/hour & nbsp 2, Time calculation: Date + number = Date (date after several days) Date-number = date (date before several days) Date-date = number (two Days of the day) Example: calculate dates after a few days select sysdate+30 from dual; ----Current date backward 30 day 3, calculate the number of months in two days, no days to display, enhance readability Syntax: month_ Between (date 1, date 2) Example: calculates the total number of months of employment for each employee to the present position. select ename, HireDate, Months_between (sysdate,hiredate) from emp; 4, add some Date after month Syntax: add_months (date, number of months) Example: Backward four-month date from current date select add_months (sysdate,4) from dual; 5, calculates the last day of the month in which the specified date is located Syntax: Last_day ( Date) Example: Calculate the current time of the last day of the month select last_day (sysdate) from dual; 6, calculate the date of any day of the next week of the specified date Grammar: next_day (date, week (one, two or three ...) Day) example: Calculate the specific date for the next Tuesday SELECT next_day (sysdate, ' Tuesday ') from dual; Conversion Functions So the so-called conversion function is the implementation of string and date, number conversion. There are three types of conversion functions: To_char (), To_date (), To_number (). 1. Convert a string function, a number or a date can be converted to a string. Syntax: To_char (column | date | number, conversion format) There are two main types of formats for conversion formats:-Date to string: year (yyyy), month (mm), day (DD), Time (hh, hh24), Min (mi), second (SS) ;-the number is converted to a string: any one digit (9), Currency (L, local currency). Example: Formatted output date Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual; example: Implementing a number conversion, increasing readability select To_char (456159784, ' l999,999,999 ') from dual; Note: The conversion above converts the date data to a string, which actually destroys the program's consistency. but the To_char () function is also good: he can divide the year, month and day. Example: Querying the number, name, and year of employment of each employee select Empno, ename, To_char (hiredate, ' yyyy ') yearly from EMP; example: Querying all employee information that was hired in February
SELECT * from emp WHERE to_char (hiredate, ' mm ') = ' 02 '; 2. Conversion Date function Syntax: To_date (string, conversion format) conversion format: Year (yyyy), month (mm), day (DD), Time (hh, hh24), Min (mi), second (SS); Example: implement string conversion to date SELECT to_date (' 199 8-09-19 ', ' YYYY-MM-DD ') from dual; 3. The turn number function converts a character composed of numbers into a numeric syntax: To_number (character) Example: SELECT to_number (' 1 ') + to_number (' 2 ') from dual; V. General function 1, null (NULL) handler: NVL reason: Because null and any data are computed at the time of calculation, the result is empty, which does not conform to our principle of data processing, so we should use the zero when we find the data we need to calculate null. Syntax: NVL (column |null, substituted value). If the contents of the column now are null, then the default value is used instead (usually with zero). Example: Comm exists empty, this column is used to replace the null data with the select Empno, ENAME,JOB,SAL,NVL (comm,0), (SAL+NVL (comm,0)) *12 income from EMP;
Single-line function in Oracle database
