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
Aggregate functions
Date functions
Other functions
String functions
 
 
 
 
 
SqlString functionsTop

LEFT(S , N): Returns the first N characters of string S from the left.
Example: LEFT('Function',6)='Functi'

RIGHT(S , N) : Returns the last N characters of string S from the right.
Example: RIGHT('Function',6)='nction'

LEN (S): Returns the number of characters in string S.
Example: LEN ('Function',6)=8

LOWER (S): Return string S after converting all characters to lower case.
Example: LOWER ('Function')='function'

UPPER (S): Return string S after converting all characters to upper case.
Example: UPPER ('Function')='FUNCTION'

LTRIM (S): Return string S after removing all blank characters from the left.
Example: LTRIM ('    Function')='Function'

RTRIM (S): Return string S after removing all blank characters from the right.
Example: RTRIM ('Function    ')='Function'

REPLACE ( S1 ,S2 ,S3 ): Return S1 after replacing all occurrence of S2 in it with S3
Example: REPLACE ('Function','n','123')='Fu123ctio123'

REPLICATE ( S , N): Return a repetition of string S, N times
Example: REPLICATE ('abc',3)='abcabcabc'

REVERSE ( S) : Return string S after reversing the order of all characters.
Example: REVERSE ('Function')=' noitcnuF'

SPACE(N): Return a string of repeated spaces N times.
Example: SPACE(4)='    '

STUFF ( S , I, N, S1): Return string S after deleting N characters from index I and inserting S1 at position I.
Example: STUFF ('Function', 3, 4,'abc'  )='Fuabcon'

SUBSTRING ( S, I, N): Return a portion of S from index I of N characters.
Example: SUBSTRING ('Function', 3, 4)='ncti'


SqlDate functionsTop

The date sections valid in date time functions are :Year, quarter, Month, dayofyear, Day, Week, Hour, minute,  second, millisecond.

DATEADD (S, N, D ): Return date D added of N unit of section S(Year, Month, Day, ...).
Example: DATEADD(Month,2,'01/24/2001')=2001-03-24 00:00:00.000

DATEDIFF ( S, D1 ,D2 ) : Return the date and time difference between date D1 and date D2 base on section S(Year, Month, Day, ...)
Example: DATEDIFF (Month,'01/24/2001','01/24/1997')=-48

DATENAME ( S, D): Return the string name of the section S in date time D.
Example: DATENAME (dayofyear,'01/24/2001')='January'
         DATENAME (dayofyear,'06/12/2001')=163

DAY(D): Return the integer value of the Day section of the specified date D
Example: DAY (,'01/24/2001')=24

MONTH ( D): Return the integer value of the Month section of the specified date D

YEAR( D): Return the integer value of the Year section of the specified date D

GETDATE ( ): Return the current database engine system date time.

GETUTCDATE(): Return the current UTC time zone date time.


SqlAggregate functionsTop

AVG ( [ ALL | DISTINCT ] E ) :  Return the average of not null values of expression E. If ALL parameter is specify the function is apply to all values, it is the default value. If DISTINCT parameter is specify the function is apply only on each occurrence of the value.

Example:  SELECT HOURS FROM Employee
   returns (7, 8, 10, 5, 4, 6, 8, 7, 11, 10, 12)
   SELECT AVG(HOURS) FROM Employee
   returns (8)
   SELECT AVG(DISTINCT HOURS) FROM Employee
   returns (7)

COUNT ( [ ALL | DISTINCT ] E ]) : Return the number of item in the group of expression E. If E=* the function will return the number of record in the data source.  If ALL parameter is specify the function is apply to all values, it is the default value. If DISTINCT parameter is specify the function is apply only on each occurrence of the value. The parameter ALL and DISTINCT can not be use with *.

MAX(E): Return the maximum value of expression E.

Example:  SELECT HOURS FROM Employee
          returns (7, 8, 10, 5, 4, 6, 8, 7, 11, 10, 12)
          SELECT MAX(HOURS) FROM Employee
          return (12)

MIN(E): Return the minimum value of expression E.

Example:  SELECT HOURS FROM Employee
          returns (7, 8, 10, 5, 4, 6, 8, 7, 11, 10, 12)

          SELECT MIN(HOURS) FROM Employee     
          return (4)

SUM ( [ ALL | DISTINCT ] E): Return the SUM of not null values of expression E. If ALL parameter is specify the function is apply to all values, it is the default value. If DISTINCT parameter is specify the function is apply only on each occurrence of the value. SUM can be use numeric columns only.

Example:  SELECT HOURS FROM Employee
   returns (7, 8, 10, 5, 4, 6, 8, 7, 11, 10, 12)
   SELECT SUM(HOURS) FROM Employee     
   returns (88)      
   SELECT SUM(DISTINCT HOURS) FROM Employee
   returns (63)


SqlOther functionsTop

Cursor Functions

@@FETCH_STATUS: Returns a flag describing the status the cursor FETCH instruction.

Values
0:  FETCH was successful.
-1: FETCH failed.
-2: No Row was found.

You last visited
COM +
DML Sql Server
 
 
Make a secure donation now with PayPal!