|
|
| PLSQL | Numeric Functions | Top |
|
ROUND(N , x) : Return the number N rounded to x decimal places. If x <0 it rounds N to x places to the left.
Example: ROUND(1234.468,2) returns 1234.5
ROUND(1234.468,-2) returns 1200
TRUNC(N , x): Return the number with the specified x decimal places. If x<0 it zeroed the number at X positions to the left.
Example: TRUNC(123.456,2) returns 123.45
TRUNC(123.456,-2) returns 100
SIGN (N): Return 1 if N is positive, 0 if N is 0 and -1 if N is negative.
Example: SIGN(-234) returns -1
SIGN(452) returns 1
ABS(N): Returns the absolute value of number N. It basically removes the sign from the number and return the positive number.
Example: ABS(-541) returns 541.
GREATEST(V1,V2,V3,…Vn): Return the largest of the n values in the list. Not to be confuse with MAX witch return the largest values of a set of rows
LEAST(V1,V2,V3,…Vn): Return the smallest of the n values in the list. Not to be confuse with MIN witch return the smallest values of a set of rows
MOD (N, x): Return the remainder of N divided by x.
Example: MOD(10,4) returns 2. MOD(5,2) returns 1
CEIL(N): Returns the smallest integer greater than or equal to N.
Example: CEIL(123.456) returns 124. CEIL(-123.456) returns -123
FLOOR(N): returns largest integer that is less than or equal to N.
Example: FLOOR (123.456) returns 123. FLOOR (-123.456) returns -124
|
|
| PLSQL | String Functions | Top |
|
SUBSTR(S,x,n): Return a portion of string S starting at position x of n characters. If x=0 it will be consider 1 as Oracle consider the first character to be at position 1. if x <0 the count will begin at the end of the string S.
Example: SUBSTR(‘abcdefg’,3,3) will return ‘cde’. SUBSTR(‘abcdefg’,-3,3) will return ‘efg’
LOWER(S): Returns the string S converted to all lowercase characters.
Example: LOWER(‘AbcDefg’) returns ‘abcdefg’
UPPER(S): Returns the string S converted to all uppercase characters.
Example: UPPER(‘AbcDefg’) returns ‘ABCDEFG’
CONCAT(S1,S2): Returns a concatenated string value of S1, S2. could be replace by S1||S2.
Example: CONCAT(‘Abc’,’DeF’) returns ‘AbcDeF’
INITCAP(S): Return the string S converted to the initial capital letters.
Example:
LENGTH(S): Return the number of characters in string S.
Example: LENGTH(‘abcdefg’) returns 7.
LPAD(S,N,X): Returns the string S concatenate to the left by characters X to the total size of N.
Example: LPAD(‘MyChar’, 10, ‘O’) returns ‘OOOOMyChar’
RPAD(S,N,X): Returns the string S concatenate to the rigth by characters X to the total size of N.
Example: RPAD(‘MyChar’, 10, ‘O’) returns ‘MyCharOOOO’
LTRIM(S): Returns the string S after cleaning all empty character to the left.
Example: LTRIM(‘ MyChar ’) returns ‘MyChar ’
RTRIM(S): Returns the string S after cleaning all empty character to the Rigth.
Example: RTRIM(‘ MyChar ’) returns ‘ MyChar’
TRIM(S): Returns the string S after cleaning all empty character to the Rigth and Left.
Example: TRIM(‘ MyChar ’) returns ‘MyChar’
REPLACE(S,S1,S2): Return string S with all occurrences of string S1 replaced by S2.
Example: REPLACE(‘abcdefabcdefabcdef’,’de’,’A’) returns ‘abcAfabcAfabcAf’
|
|
| PLSQL | Date Functions | Top |
|
ADD_MONTHS(D, m): Return the date D added with the specified number of months. If m<0 Oracle subtract the number of month by m.
Example: ADD_MONTHS(‘12-JAN-2002’,9) returns ‘12-OCT-2002’.
ADD_MONTHS(‘12-JAN-2002’,-1) returns ‘12-DEC-2001’.
LAST_DAY(D): Return the last day of the month for the date D.
Example: LAST_DAY(‘12-DEC-2001’) returns ‘31-DEC-2001’
MONTHS_BETWEEN(D1,D2): Return the fractional difference in months between date D1 and date D2, if D1<D2 the difference is negative. This function takes in account days and hours.
NEXT_DAY(D,DayName): Return the date of the very next day of DayName specified, after the date D.
Example: NEXT_DAY(‘12-DEC-2005’,’SUNDAY’) returns ‘18-DEC-2005’
TRUNC(D): Return date D without its time component.
ROUND (D, format): Return the date D rounded to the unit specified by the format, if no format if specified the date D is return rounded to the nearest day.
Example: ROUND(‘12-DEC-2004’,’YEAR‘) returns ‘1-JAN-2005’
SYSDATE: Return the database server’s current datetime.
|
|
| PLSQL | Grouping Functions | Top |
|
MIN(E): Return the smallest value of expression E from a set of rows.
MAX(E): Return the largest value of expression E from a set of rows.
SUM(E): Return the summed value of expression E from a set of rows.
AVG(E): Returns the average value of expression E from a set of rows.
COUNT(E): Return the number of row from the set with the value of E not null. If E=* the function will simply return the number of rows in the set.
|
|
| PLSQL | Conversion Functions | Top |
|
TO_DATE(S,format): Return a Date from the conversion of string S, in the format specify, to date time.
The standard of date time format in Oracle is ‘DD-MON-YYYY’
TO_CHAR(N,format): Return a string from the conversion of number N, in the format specify, to string.
TO_CHAR(D,format): Return a string from the conversion of date D, in the format specify, to string.
TO_NUMBER(S, format): Return a number from the conversion of string S, in the format specify, to number.
|
|
|
|
