Oracle's SQL functions

1.SQL function
(1) Character function: mainly manipulating strings
(2) Number function: The number of numeric types is mainly manipulated
(3) Conversion functions: These functions convert data from one data type to another data type
(4) Date function: processing date and Time
(5) Regular expression functions: These functions use regular expressions to search for data
2. character functions
(1) CONCAT (m,n) joins M and N and returns the concatenated string
(2) initcap (n) converts the first letter in N to uppercase
(3) INSTR (X,y,m,n) finds the position where the string y appears in X. Where m,n is optional, M represents the start of the search from the first m position of the string x. n Indicates the number of occurrences of the string y
(4) Length (n) to find the lengths of the string n
(5) LOWER (n) convert each character in string n to lowercase
(6) Lpad (x,n,y) blanks the left side of the string x, resulting in a string with a total length of n characters. Where y is optional, this parameter is used to specify the string to be padded to the left
(7) Rpad (X,n,y) is similar to Lpad, just to the right of the string x
(8) TRIM ([leading | trailing | both [Trim_character]] FORM x)By default, the specified characters are truncated to the left and right of X, where leading, trailing, both, and trim_character are optional.
if the parameter leading is specified, the specified character to the left of X is truncated. If the parameter trailing is specified, the specified character to the right of X is truncated. If the parameter both is specified, the specified characters on the left and right sides of the X are truncated. Parameter Trim_character specifies the character to intercept
(9) LTRIM (x, y) truncates the specified character to the left of X. The specified character can be represented by the parameter Y. If you do not specify a character to truncate, the space is truncated by default
RTRIM (x, y) is similar to LTrim, except that the specified character is truncated to the right of the string x
(one) NVL (x, y) if x is null, the result returns Y; otherwise the result returns x
(NVL2) if x is not NULL, the result returns Y; otherwise the result returns Z
NANVL (x, y) if the value of X is Nan (not a number), the result returns Y; otherwise the result returns X. Oracle 10g new features
replace (x, y, z) replaces the character or string y with the z
UPPER (x) converts each character in string N to uppercase
(+) SUBSTR (x,m,n) obtains a substring of length n from the m position of the character X, where n can be omitted from writing,then the substring is obtained from the beginning of M to the end of the string

3. Numeric Functions
(1) ABS (x) to find the absolute value of x
(2) Floor down rounding (=< x)
(3) ceil upward rounding (>=x)
(4) ROUND (x, y) takes an integer to X., and y is an optional parameter that indicates rounding of the first decimal. if Y is a positive number, the rounding starts from the Y-bit to the right of the decimal point of X, and if Y is negative, starts rounding from the Y-position to the left of the decimal point of X
(5) TRUNC (x, y) is truncated, and y is an optional parameter that indicates truncation of the fractional number. If Y is a positive number,truncated from the Y-bit to the right of the decimal point of X, and if Y is negative, truncates from the Y-bit to the left of the decimal point of X
(6) MOD (x, y) to find the remainder of x divided by Y
4. Date function
(1) add_months (x, y) month +y
(2) last_day (x) to find out the last day of this month
(3) months_between (x, y) returns the difference between x and Y (y<x)
(4) next_day (x,day) returns the date of the next day starting with X
(5) Oracle gets the current date and date format
Get system Date: Sysdate ()
formatted Date: To_char (Sysdate (), ' Yy/mm/dd HH24:MI:SS)or To_date (Sysdate (), ' Yy/mm/dd HH24:MI:SS)
formatted number: To_number

Note: To_char converts a date or number to a string
To_char (number, ' format ')
To_char (Salary, ' $99,999.99 ')
To_char (date, ' format ')
To_date converting a string to a date type in a database
To_date (char, ' format ')
To_number converting a string to a number
To_number (char, ' format ')

(6) Return the system date, Output 2 May-December -09
select Sysdate from dual;
mi is minutes, output 2009-12-25 14:23:31
Select To_char (sysdate, ' Yyyy-mm-dd HH24:mi:ss ') from dual;
mm will display month, output 2009-12-25 14:12:31
Select To_char (sysdate, ' Yyyy-mm-dd HH24:mm:ss ') from dual;
Output 09-12-25 14:23:31
Select To_char (sysdate, ' Yy-mm-dd hh24:mi:ss ') from dual
Output 2009-12-25 14:23:31
Select To_date (' 2009-12-25 14:23:31 ', ' Yyyy-mm-dd,hh24:mi:ss ') from dual
and if you write on the writing:
Select To_date (' 2009-12-25 14:23:31 ', ' Yyyy-mm-dd,hh:mi:ss ') from dual
will be error, because the hour HH is 12 binary, 14 is illegal input, can not match.
Output $10,000,00:
Select To_char (1000000, ' $99,999,99 ') from dual;
Output rmb10,000,00:
Select To_char (1000000, ' l99,999,99 ') from dual;
Output 1000000.12:
Select Trunc (To_number (' 1000000.123 '), 2) from dual;
Select To_number (' 1000000.123 ') from dual;
format of conversion:
representing year: Y represents the last of the years,
yy represents the last 2 digits of the year,
YYY represents the last 3 digits of the year,
yyyy 4-digit year
indicates: mm with 2 digits for month,
mon in shorthand form, such as November or Nov,
month with full name, like November or November.
represents day: DD indicates days of the month,
DDD indicates the day ordinal of the year
dy When in a few days, shorthand, such as Friday or Fri,
day when in days, full name, such as Friday or Friday
represents hour: HH 2 digits represent hours 12 decimal,
hh24 2-digit hour 24 hour
represents minute: mi 2 digits for minutes
represents second: SS 2 digits for seconds 60 binary
indicates quarterly: Q One number represents quarter (1-4)
There is also WW used to indicate the week of the year when W is used to denote the month ordinal.
time range under 24-hour system: 00:00:00-23:59:59
time range Under 12-hour system: 1:00:00-12:59:59
Number format: 9 represents a number
0 Force display 0
$ place a $ character
L Place a floating local currency symbol
. Show decimal points
, displays the thousand indicator
Add:
time minus 7 minutes for the current time
Select Sysdate,sysdate-interval ' 7 ' MINUTE from dual;
the current time minus 7 hours of time
Select Sysdate-interval ' 7 ' hour from dual;
the current time minus 7 days
Select Sysdate-interval ' 7 ' Day from dual;
time minus July for current time
Select Sysdate,sysdate-interval ' 7 ' month from dual;
time minus 7 years in the current time
Select Sysdate,sysdate-interval ' 7 ' year from dual;
time interval multiplied by a number
Select Sysdate,sysdate-8*interval ' 7 ' hour from dual;
meaning Explanation:
dual pseudo-column
Dual is an actual table in Oracle that can be read by any user, often in a SELECT statement block without a target table.
different systems may return dates that are not in the same format.
returns the currently connected User: Select User from dual;

6. Conversion functions
(1) bin_to_num (x) converts x to a binary number
(2) To_char (X[,format]) converts x to a VARCHAR2 string. Format is an optional parameter to specify the formatting of X
(3) Chartorowid (x) converts x to ROWID type, rowid length is 18 bits
(4) To_date (X[,format]) converts x to a DATE. Format is an optional parameter to specify the formatting of X
(5) To_number (X[,format]) converts x to a number. Format is an optional parameter to specify the formatting of X

(8) Bin_to_num (x) converts x to decimal digit x format 1,1,1
(9) Chartorowid (x) converts x to rowID type, rowid to 18-bit
(Ten) Rowidtochar (x) converts rowID x to a VARCHAR2 type character
(one) Rowidtonachar (x) converts rowID x to a NVARCHAR2 type character
To_char (X[,format]) converts x to a varchar2 string. Format is an optional parameter. Used to specify the format of x
Note: numbers are implicitly converted
To_date (X[,format]) converts x to a date. Format is an optional parameter to specify the formatting of X
(+) To_number (X[,format]) converts x to a number. Format is an optional parameter to specify the formatting of X
7. Numeric Functions
ABS (x) Seek absolute value
ASIN (x) to find the inverse sine function
ACOs (x) to find the inverse cosine function
Atin (x) to find the inverse tangent function
cos (x) to find cosine function
sin (x) to find the sine function
*ceil (x) to find the smallest integer greater than or equal to x
*floor (x) to find the largest integer less than or equal to x
*round (x, y) takes an integer, and y is an optional parameter that represents the rounding of the first decimal. If Y is a positive number, start rounding from Y to the right of the decimal point of X and, if Y is negative, start rounding from the left of the decimal point of the X, rounding
*trunc (x, y) truncates ×, and y is an optional parameter that indicates truncation of the fractional number. If Y is positive, it is from the right of the decimal point of X and vice versa
mod (x, y) to find the remainder of x divided by Y

8. Example Application

(1) Examples of numerical functions:
Floor: Take Small
Select Floor (5.8) from dual; The return value is 5
Ceil: Take da
Select Ceil (5.8) from dual; The return value is 6
Round
Select Round (5.89), round (5.89,1), round (5.89,-1) from dual; The return value is 6,5.9,10
Trunc
Select Trunc (5.89), trunc (5.89,1), Trunc (5.89,-1) from dual; The return value is 5,5.8,0

(2) Example of date function

add_months:
Select Add_months (sysdate,1) from dual;
Last_day:
Select Last_day (sysdate) from dual;
Next_day:
Select Sysdate,next_day (sysdate,5) from dual;
Months_between:
Select Months_between (sysdate,sysdate+1) from dual;
Round: To the annual rounding, the month day becomes 1; on the month, the day becomes 1
Select Round (sysdate), round (sysdate, ' yyyy '), round (sysdate, ' mm ') from dual;
Trunc: The truncation of the back in Y units
trunc (sysdate), trunc (sysdate, ' yyyy '), trunc (sysdate, ' mm ') from dual;
Both date and timestamp can be added and reduced.
There are three ways to manipulate different time types for the current date Gagnin, month, day, hour, minute, and second:

1 add hours, minutes and seconds using built-in functions Numtodsinterval
2 plus a simple number to increase the day
3 use built-in function add_months to increase year and month

Cases:
Add one hours to the current date:
Sql> Select Sysdate, Sysdate+numtodsinterval (1, ' Hour ') from dual;

Sysdate Sysdate+numtodsinte
——————- ——————-
2010-10-14 21:38:19 2010-10-14 22:38:19
Add 50 minutes to the current date
Sql> Select Sysdate, Sysdate+numtodsinterval (' minute ') from dual;

Sysdate Sysdate+numtodsinte
——————- ——————-
2010-10-14 21:39:12 2010-10-14 22:29:12
Add 45 seconds to the current date
Sql> Select Sysdate, Sysdate+numtodsinterval ("second") from dual;

Sysdate Sysdate+numtodsinte
——————- ——————-
2010-10-14 21:40:06 2010-10-14 21:40:51
Add 3 days to the current date
Sql> Select Sysdate, sysdate+3 from dual;

Sysdate sysdate+3
——————- ——————-
2010-10-14 21:40:46 2010-10-17 21:40:46
Add 4 months to the current date
Sql> Select Sysdate, Add_months (sysdate,4) from dual;

Sysdate add_months (Sysdate,
——————- ——————-
2010-10-14 21:41:43 2011-02-14 21:41:43

2 year increase in current date
Sql> Select Sysdate, Add_months (sysdate,12*2) from dual;

Sysdate add_months (Sysdate,
——————- ——————-
2010-10-14 21:42:17 2012-10-14 21:42:17

timestamp the same way as above;
ask for two dates difference:

sql> Select Sysdate-to_date (' 20070523 21:23:34′, ' yyyy-mm-dd hh24:mi: SS ') DT from
dual;

Dt

1240.01623
If the two date is directly subtracted, the result is a data type, we may want to get two date difference in the expression form:
* * * * * * * * * * * *:* *:* *
Sql> SELECT Numtoyminterval (Months_between (DT1, DT2), ' month ') Mon,
2 Numtodsinterval (dt1-(Add_months (Dt2,trunc (Months_between, DT1))
"Day") Day
3 from (SELECT sysdate DT1,
4 to_date (' 20070523 21:23:34′, ' yyyy-mm-dd hh24:mi:ss ') DT2
5 from DUAL)
6;

MON Day
—————- ———————-
+000000003-04 +000000021 00:40:15.999999999

namely: 3 years 4 months 21 days 00:40:15.99999999
Oracle aggregate functions:
Count returns the number of records found
count (column name) the number of records in the column
There are several records in the Count (*) Table
min Returns the minimum value of a numeric column or computed column
Max Returns the maximum value of a numeric column or computed column
sum returns the sum of a numeric column or computed column
avg averages a numeric column or computed column
*group BY clause
Warning:
(1) If the query contains aggregate functions, and the selected columns are not in the aggregate function, then the columns must be in the GROUP BY clause.
(2) You cannot use aggregate functions in the WHERE clause to restrict rows. Rows can only be restricted by having clauses.
GROUP by can be used primarily to group rows into multiple parts with the same column values.
The use of group by is not very meaningful if it is used solely for grouping rows.
In complex SQL queries, the GROUP BY clause occurs most of the time in aggregate functions together
If the query contains an aggregate function, and the selected column is not in the aggregate function,
then these columns must be in the GROUP BY clause;
the HAVING clause can limit the aggregation function.

Oracle's SQL functions