About us |  Help us improve
intelliVERB - Get a fair search of the Internet !
Visual studio .net
SQL Server
Oracle
Main  >  Basic functions Email this page to friend
Conversion Functions
Date Functions
Grouping Functions
Numeric Functions
String Functions
 
 
 
 
 
PLSQLNumeric FunctionsTop

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


PLSQLString FunctionsTop

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’


PLSQLDate FunctionsTop

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.


PLSQLGrouping FunctionsTop

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.


PLSQLConversion FunctionsTop

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.

You last visited
Remoting
Web Objects
Inheritance C# .net
XML
Assemblies
DDL Sql Server
Sql Server Basic functions
COM +
DML Sql Server
 
 
Make a secure donation now with PayPal!