Single-line function |
| Classification |
Function |
Function |
Example |
| Character functions |
Lpad (<C1>,<I>[,<C2>]) |
Add a string to the left of the string C1 C2 until the length of the C1 string equals I. |
SELECT lpad (' hello! ', 9,1) leftpad,rpad (' hello! ', 8,2) Rightpad from DUAL |
| Rpad (<C1>,<I>[,<C2>]) |
Add the string C2 to the right of the string c1 until the length of the C1 string equals I. |
| LOWER (<c1>) |
Convert string C1 to lowercase |
SELECT LOWER (' ename ') one,upper (' ename '), Initcap (' ename ') from dual |
| UPPER (<c1>) |
Convert string C1 to uppercase |
| Initcap (<c1>) |
Converts the first letter of each word of a C1 string to uppercase |
| LENGTH (<c1>) |
Returns the length of the string C1 |
SELECT LENGTH (' How is ') from DUAL |
| SUBSTR (<C1>,<I>[,<J>]) |
Returns the J character (right) starting at the first position in the string C1. If J is omitted, all characters starting at the first position in C1 are returned. If J is negative, the J character (left) starting at the first position in the string C1 is returned. |
SELECT SUBSTR (' Hello,world ', 1,5) from DUAL |
| INSTR (<C1>,<C2>[,<I>[,<J>]) |
From position I in C1 to find the position of C2 in C1, I can be negative (at this point, from the end of the C1). |
SELECT INSTR (' Mississippi ', ' I ', 3,3) from DUAL; Returns the result 11. SELECT INSTR (' Mississippi ', ' I ', -2,3) from DUAL; Returns the result 2. |
| LTRIM (<c1>,<c2>) |
Remove any leading character sets that appear in C2 from the front of the C1. |
SELECT LTRIM (' Mississippi ', ' Mis ') from DUAL ; Returns the result ' PPI '.
SELECT RTRIM (' Mississippi ', ' IP ') from DUAL; Returns the result ' Mississ '.
|
| RTRIM (<c1>,<c2>) |
Remove any leading character sets that appear in C2 from behind C1. |
| Mathematical functions |
ABS (<n>) |
Returns the absolute value of n |
SELECT ABS ( -2), ABS (2) from DUAL; |
| ROUND (<n1>,<n2>) |
Retains the N2 bit (rounded) and returns the N1 after the decimal point. If N2 is less than 0, N1 rounds to the left of the decimal point. |
SELECT ROUND (12345.678,-2), ROUND (12345.678,2) from DUAL; Return the result separately: 12300,12345.68. |
| Ceil (<n>) |
The n is rounded up and returned. |
SELECT ceil (5.1), Ceil ( -21.4) from DUAL; Return: 6,-21, respectively. |
| Floor (<n>) |
The n is rounded down and returned. |
SELECT Floor (5.1), floor ( -21.4) from DUAL; Return: 5,-22, respectively. |
| MOD (<n1>,<n2>) |
Returns the remainder of the N1 modulo N2. |
SELECT MoD (14,5), mod (8,25), mod ( -64,7) from DUAL; Return the result separately: 4,8,-1. |
| Sign (<n>) |
Symbolic function, n>0, returns 1. N<0, returns-1. N=0, returns 0. |
SELECT sign ( -2.3), sign (2.3), sign (0) from DUAL;
Results returned: -1,1,0 |
| SQRT (<n>) |
Returns the square root of n |
SELECT SQRT (9) from DUAL; Results returned: 3 |
| TRUNC (<n1>,<n2>) |
Functions like the round function. But do not do rounding. |
SELECT TRUNC (123.456,2), TRUNC (123.456,-1) from DUAL; Results were returned: 123.45, 120. |
| Vsize (N) |
Returns the storage bytes of the number n |
SELECT Vsize (123) from DUAL; Returns: 3 |
| Date functions (dates can be arithmetic operations) |
Sysdate |
Return to System date |
SELECT sysdate from DUAL; |
| Add_months (<d>,<i>) |
Returns the Date D plus the new date after I month (I positive negative). |
SELECT sysdate, Add_months (sysdate,2), Add_months (sysdate,-2) from DUAL |
| Last_day (<d>) |
Returns the last day of the month on which the date D is located. |
SELECT Sysdate,last_day (sysdate) from DUAL |
| Months_between (<d1>,<d2>) |
Returns the number of months that the date D1 is larger than D2. |
SELECT months_between (' 19-dec-1999 ', ' 19-mar-2000 ' from DUAL; |
| New_time (<d>,<tz1>,<tz2>) |
Convert time zone TZ1 D to time in the timezone tz2. |
SELECT sysdate,new_time (sysdate, ' CDT ', ' PDT ') from DUAL; |
| Next_day (<d>,<dow>) |
Returns the first Dow after date d. (Dow:day of week) |
SELECT next_day (sysdate, ' Monday ') from DUAL; |
| Common conversion functions |
To_char (<X>[,<FMT>[,<NLSPARM>]) |
Converts x into a string. (See Oracle's online Help for parameter implications) |
SELECT to_char (sysdate, ' Yyyy-mm-dd ') from DUAL; |
| To_number (<C>[,<FMT>[,<NLSPARM>]) |
Converts the string C to a number. (See Oracle's online Help for parameter implications) |
SELECT to_number (' 123 ') from DUAL ; |
To_date (<C>[,<FMT>[,<NLSPARM>]) (Please check the online Help for the common date format.) ) |
Converts the string C to a date. |
SELECT to_date (' 19-mar-99 ', ' dd-mon-yyyy ') from DUAL; |
| Two important functions |
DECODE (<X>,<M1>,<R1>[,<M2>, <r2...>][,<d>]) (The Decode function is very powerful, please be careful to ponder.) ) |
A very powerful function that makes SQL very efficient. It functions like a series of if...then...else statements. |
SELECT Sid,serial#,username, DECODE (Command , 0, ' None ' , 2, ' Insert ' , 3, ' Select ' , 6, ' Update ' , 7, ' Delete ' , 8, ' Drop , ' other ') cmd From v$session WHERE type<> ' BACKGROUND '; |
NVL (X1,X2) Note the null value in Oracle, and note that the function |
If X1 returns x2, the X1 is returned. |
SELECT NVL (ename, ' No Name ') from EMP; |
Grouping functions |
| |
AVG ([{distinct| all}]<n>) |
To return the average of a specified column of rows |
SELECT avg (SAL), avg (all Sal), avg (DISTINCT sal) From SCOTT. EMP; |
| COUNT ({*|[ Distinct| all]}<x>) |
Statistics the number of rows returned |
SELECT Count (*), COUNT (DISTINCT Mgr), COUNT (Mgr) From SCOTT. Emp |
| MAX ([{distinct| all}]<x>) |
Returns the maximum value of the specified column of the row |
SELECT Max (SAL), MAX (DISTINCT sal) from EMP; |
| MIN ([{distinct| all}]<x>) |
Returns the minimum value of the specified column of the row |
SELECT min (sal), MIN (DISTINCT sal) from EMP; |
| STDDEV ([{distinct| all}]<x>) |
Returns the standard variance of the specified column of a row |
SELECT STDDEV (SAL), STDDEV (DISTINCT sal) from EMP; |
| SUM () |
Returns the specified column of the row and the |
SELECT SUM (SAL) from EMP; |
| VARIANCE () |
To return the difference value of the specified column of a row |
|
