Oracle Common functions

One, character functions

Character functions accept character arguments, which can be columns in a table or a string expression.

Commonly used character functions:

Function	Description
ASCII (X)	Returns the ASCII code of the character X
CONCAT (x, y)	Connection string x and Y
INSTR (X,str[,start][,n)	To find str from x, you can specify start from start or start with n
LENGTH (X)	Returns the length of X
LOWER (X)	x convert to lowercase
UPPER (X)	X converted to uppercase
LTRIM (X[,trim_str])	Truncate X to the left of the TRIM_STR string, the default is to truncate the space
RTRIM (X[,trim_str])	Truncate x to the right of the TRIM_STR string, the default truncation of space
TRIM ([Trim_str from]x)	Truncate the TRIM_STR string on both sides of X, and truncate the space by default
REPLACE (x,old,new)	Find old in X and replace it with new
SUBSTR (X,start[,length])	Returns the string of X, starting at start, intercepting length characters, default length, defaults to end

Examples of the above functions:

Example	Example results
SELECT ASCII (' a ') from dual;	97
SELECT CONCAT (' Hello ', ' world ') from dual;	Helloworld
SELECT INSTR (' Hello world ', ' or ') from dual;	8
SELECT LENGTH (' Hello ') from dual;	5
SELECT LOWER (' Hello ') from dual;	Hello
SELECT UPPER (' Hello ') from dual;	HELLO
SELECT LTRIM (' =hello= ', ' = ') from dual;	hello=
SELECT RTRIM (' =hello= ', ' = ') from dual;	=hello
SELECT TRIM (' = ' from ' =hello= ') from dual;	Hello
SELECT REPLACE (' ABCDE ', ' CD ', ' AAA ') from dual;	Abaaae
SELECT SUBSTR (' ABCDE ', 2,3) from dual;	Bcd

Second, the number function

Numeric functions accept numeric arguments, which can come from a column in a table or a numeric expression.

Function	Description	Example
ABS (X)	Absolute value of X	ABS (-3) =3
ACOS (X)	The inverse cosine of x	ACOS (1) =0
COS (X)	Cosine	COS (1) =0.54030230586814
Ceil (X)	Minimum value greater than or equal to X	Ceil (5.4) =6
Floor (X)	Maximum value less than or equal to X	Floor (5.8) =5
LOG (x, y)	X is the logarithm of the bottom y	LOG (2,4) =2
MOD (x, y)	The remainder of x divided by Y	MOD (8,3) =2
POWER (x, y)	Y power of X	POWER (2,3) =8
ROUND (X[,y])	X rounding in the Y position	ROUND (3.456,2) =3.46
SQRT (X)	Square root of X	SQRT (4) =2
TRUNC (X[,y])	X truncated at the y bit	TRUNC (3.456,2) =3.45

Description

1. ROUND (X[,y]), rounded.

At default y, the default is y=0; for example: ROUND (3.56) = 4.

Y is a positive integer that is rounded to the Y-bit after the decimal point. ROUND (5.654,2) = 5.65.

Y is a negative integer rounded to the left of the decimal |y| bit. ROUND (351.654,-2) = 400.

2. TRUNC (X[,y]), direct interception, not rounded.

At default y, the default is y=0; for example: TRUNC (3.56) = 3.

Y is a positive integer that is rounded to the Y-bit after the decimal point. TRUNC (5.654,2) = 5.65.

Y is a negative integer rounded to the left of the decimal |y| bit. TRUNC (351.654,-2) = 300.

Third, date function

Date functions perform operations on dates. The commonly used date functions are:

1, add_months (d,n), on a date D, plus the specified number of months n, returns the calculated new date.

D represents the date, and N indicates the number of months to add.

Example: SELECT sysdate,add_months (sysdate,5) from dual;

2, Last_day (d), returns the last day of the month of the specified date.

Example: SELECT sysdate,last_day (sysdate) from dual;

3, ROUND (D[,fmt]), returns a rounding date value in FMT format, D is the date, FMT is the format

Model. The default FMT is DDD, which is the day of the month.

? ① If the FMT is "year", it is rounded to the January 1 of the first six months, which is the next year.

? ② If the FMT is "month", it is rounded to the 1st of the month, that is, the previous month, and the second half month as the next.

? ③ default to "DDD", that is, a day of the month, the nearest day, the first half-day to go, after half a day as the next.

? ④ If the FMT is "day" then rounds to the nearest week of Sunday, that is, the upper half of the week, the second half of the week as the next week Sunday.

Example: SELECT sysdate,round (sysdate), ROUND (sysdate, ' Day '),

ROUND (sysdate, ' month '), ROUND (Sysdate, ' year ') from dual;

When the function corresponding to ROUND TRUNC (D[,FMT]) operates on the date, TRUNC is very similar to ROUND, except that the date is not rounded and the first day of the corresponding format is intercepted directly.

4, EXTRACT (FMT from D), extracts a specific part of the date.

FMT is: Year, MONTH, Day, HOUR, MINUTE, SECOND. The year, MONTH, day can match the DATE type, or it can match the TIMESTAMP type, but HOUR, MINUTE, and SECOND must match the TIMESTAMP type.

The results of the HOUR match are not added to the time zone, so the result in China runs 8 hours less.

Example: Select Sysdate "Date",

EXTRACT (year from sysdate) "year",

EXTRACT (month from sysdate) "Month",

EXTRACT (Day from Sysdate) ' Day ',

EXTRACT (HOUR from Systimestamp) "HOUR",

EXTRACT (MINUTE from Systimestamp) "MINUTE",

EXTRACT (SECOND from Systimestamp) "SECOND"

from dual;

Iv. Conversion Functions

A conversion function converts a value from one data type to another data type. The common conversion functions are:

1, To_char (D|N[,FMT])

Converts a date and number to a formatted string. FMT is a formatted string

Code Demo: To_char Processing of dates

Select To_char (sysdate, ' YYYY ' year "MM" month "DD" Day "HH24:MI:SS ')" date "from dual;

Code parsing:

In a formatted string, use double quotation marks to reference an unformatted character

For numeric formatting, formatted characters are:

Parameters	Example	Description
9	999	Show numbers at specified locations
.	9.9	Returns the decimal point at the specified position
,	99,99	Returns a comma at the specified position
$	$999	Returns a dollar sign at the beginning of a number
Eeee	9.99EEEE	Scientific notation means
L	L999	Add a local currency symbol to the number before
PR	999PR	If a numeric negative number is represented by angle brackets

Code Demo: To_char Processing of numbers

Select To_char ( -123123.45, ' L9.9eeeepr ') "date" from dual;

2, To_date (X,[,FMT])

Converts a string into a date type in the FMT format

3, To_number (X,[,FMT])

Converts a string to a number in the FMT format

Code Demo: To_num function

Select To_number ('-$12,345.67 ', ' $99,999.99 ') "num" from dual;

Five, other single-line functions

1, NVL (X,value)

Returns value if X is empty, otherwise returns X

Example: The salary is 2000 yuan of employees, if not issued bonuses, each bonus 100 yuan

Code Demo: Nvl function

Sql> SELECT ENAME,JOB,SAL,NVL (comm,100) from EMP WHERE sal<2000;

-------------------------------------------------------------------

ename JOB SAL NVL (comm,100)

SMITH Clerk 800 100

ALLEN salesman 1600 300

WARD salesman 1250 500

MARTIN salesman 1250 1400

TURNER salesman 1500 50

ADAMS Clerk 1100 100

JAMES Clerk 950 100

-------------------------------------------------------------------

7 Rows selected

2, NVL2 (x,value1,value2)

Returns value1 if X is not empty, otherwise returns value2

Example: the EMP table in the salary of 2000 yuan employees, if there is no bonus, the bonus is 200 yuan, if there is a bonus, the original bonus based on the increase of 100 yuan

Code Demo: NVL2 function

Sql> Select ENAME,JOB,SAL,NVL2 (comm,comm+100,200) "COMM"

2 from EMP WHERE sal<2000;

-------------------------------------------------------------------

ename JOB SAL Comm

SMITH Clerk 800 200

ALLEN salesman 1600 400

WARD salesman 1250 600

MARTIN salesman 1250 1500

TURNER salesman 1500 150

ADAMS Clerk 1100 200

JAMES Clerk 950 200

MILLER Clerk 1300 200

-------------------------------------------------------------------------------------------------------

8 Rows selected

Vi. Aggregation functions

The aggregate function simultaneously operates on a set of data, returning a row of results, such as calculating the sum of a set of data, averaging

such as

Name	Role	Grammar
Avg	Average	AVG (expression)
SUM	Sum	SUM (expression)
MIN, MAX	Minimum value, maximum value	MIN (expression), MAX (expression)
COUNT	Data statistics	COUNT (expression)

Example: The sum of the basic salary for all employees this month

Code Demo: Sum function

sql> SELECT SUM (SAL) from EMP;

-------------------------------------------------------------------

SUM (SAL)

29025

To find the average wage in different departments

Code Demo: Grouping queries under the AVG function

Sql> SELECT Deptno,avg (SAL) from the EMP GROUP by DEPTNO;

-------------------------------------------------------------------

DEPTNO AVG (SAL)

---------    ----------

30 1566.66666

20 2175

10 2916.66666

Oracle Common functions