Oracle Common functions

The character functions are explained in the "Oracle Basic Queries" section

First, the numerical function

1, Round (returns the value after rounding)

Format:round (x[,y])

X, y, numeric expressions, if Y is not an integer, the y integer portion is truncated, if y>0 is rounded to a Y decimal, and if Y is less than 0 rounded to the left of the decimal point.

Eg:select round (1234,-2), round (1234.1234,2) from dual output: 1200 1234.12

Select Round (1234.1234,-2) from dual output results: 1200

2, TRUNC (for dates)

The date value that the Trunc function truncates for the specified element.

Format: TRUNC (DATE[,FMT])

which

Date a datetime value

FMT date format, which is truncated by the specified element format. Ignoring it is truncated by the nearest date.

Here's how this function is used:

TRUNC (To_date (' 24-nov-1999-5:00pm '), ' dd-mon-yyyy hh:mi am ')

= ' 24-nov-1999 12:00:00 am '

TRUNC (To_date (' 24-nov-1999 08:37 pm ', ' dd-mon-yyyy hh:mi am '), ' hh ') = ' 24-nov-1999 08:00:00 am '

Trunc (sysdate, ' yyyy ')--return to the first day of the year.

Trunc (sysdate, ' mm ')--Returns the first day of the month.

Trunc (sysdate, ' d ')--Returns the first day of the current week.

Trunc (sysdate, ' DD ')--return to current month day

2.TRUNC (for number)

The TRUNC function returns the processed value, and its working mechanism is very similar to the round function, except that the function does not make the corresponding rounding selection processing of the specified fractional number before or after it, and it is truncated altogether.

Its specific syntax format is as follows

TRUNC (Number[,decimals])

which

Number to be intercepted and processed

Decimals indicates the number of digits after the decimal point to be retained. Optional, ignore it to truncate all the decimal parts

Here's how this function is used:

TRUNC (89.985,2) =89.98

TRUNC (89.985) =89

TRUNC (89.985,-1) =80

Note: The second parameter can be a negative number, which means that the portion of the digits to the left of the decimal point is truncated to 0. Similar to rounding, such as a parameter of 1 is rounded to a very bit, if 1, is rounded to 10 bits, and so on.

The use of the--oracle trunc () function
/************** Date ********************/
1.select trunc (sysdate) from dual--2011-3-18 today's date is 2011-3-18
2.select trunc (sysdate, ' mm ') from dual--2011-3-1 returns the first day of the month.
3.select trunc (sysdate, ' yy ') from dual--2011-1-1 returns the first day of the year
4.select trunc (sysdate, ' DD ') from dual--2011-3-18 return current month day
5.select trunc (sysdate, ' yyyy ') from dual--2011-1-1 returns the first day of the year
6.select trunc (sysdate, ' d ') from dual--2011-3-13 (Sunday) returns the first day of the current week
7.select trunc (sysdate, ' hh ') from dual--2011-3-18 14:00:00 current time is 14:41
8.select trunc (sysdate, ' mi ') from dual--2011-3-18 14:41:00 trunc () function no seconds accurate
/*************** Digital ********************/
/*
TRUNC (Number,num_digits)
Number requires a truncated rounding.
The num_digits is used to specify the number of rounding precision. The default value for Num_digits is 0.
TRUNC () function is not rounded when truncated
*/
9.select trunc (123.458) from dual--123
10.select trunc (123.458,0) from dual--123
11.select trunc (123.458,1) from dual--123.4
12.select trunc (123.458,-1) from dual--120
13.select trunc (123.458,-4) from dual--0
14.select trunc (123.458,4) from dual--123.458
15.select trunc (123) from dual--123
16.select trunc (123,1) from dual--123
17.select trunc (123,-1) from dual--1203, MODThe mod function is a redundancy function, in the form of mod (NEXP1,NEXP2), which is the remainder of the two numeric expressions after the division operation. So: two integers with the same number are the same as you know two positive balance (that is, two negative integers and two positive integers ),that is, the remainder of the division of two numbers is returned after the two-digit surplus. format:MOD (number,divisor) number is the divisor;Divisor is the divisor. If divisor is zero, the function MOD returns the original numberThe function mod can be represented by a function INT: MoD (n, d) = N-d*int (n/d) Example:MoD (3, 2) equals 1MOD (-3, 2) equals -1mod (3,-2) equals 1 mod (-3,-2) equals -1mod (-3, 0) equals -3mod (3, 0) equals 3MOD (2,0) equals 2 Note: The above is the compute side of the MOD function in Oracle　tested in PL/SQL Dev. In Excel MoD (-3, 2) equals 1 (same as the following number) mod (3,-2) equals-1 (same as the following symbol) mod (3,0) error #div/0!

Oracle Common functions