This chapter provided an overview of the functions available in SQL Server . In
this chapter, we looked at several of SQL Server's aggregate, row-level and other functions. We also
presented conversion as well as date functions.
|
Aggregate Functions
|
|
AVG
|
Averages a group of row values.
|
|
COUNT
|
Counts the total number of rows in a result set.
|
|
MAX
|
Returns the highest of all values from a column.
|
|
MIN
|
Returns the lowest of all values from a column.
|
|
SUM
|
Adds all the values in a column.
|
|
Row-level Functions
|
|
ABS
|
Returns an absolute value.
|
|
CEILING
|
Returns the next larger integer value.
|
|
FLOOR
|
Returns the next lower integer value.
|
|
ISNULL
|
Returns a true value if a data item contains a NULL.
|
|
NULLIF
|
Returns a NULL if a certain condition is met in an expression.
|
|
ROUND
|
Rounds numbers to a specified number of decimal places.
|
|
STR
|
Converts from a number to a character data type.
|
|
SQRT
|
Returns the square root of positive numeric values.
|
|
SQUARE
|
Returns the square of a number.
|
|
String Functions
|
|
CHARINDEX
|
Returns the starting position of a specified pattern.
|
|
LEFT
|
Returns the left portion of a string up to a given number of characters.
|
|
LEN
|
Returns the length of a string.
|
|
LIKE
|
Option that matches a particular pattern.
|
|
LOWER
|
Converts a string to lower case.
|
|
RIGHT
|
Returns the right portion of a string.
|
|
RTRIM
|
Removes blanks from the right end of a string.
|
|
SUBSTRING
|
Returns part of a string.
|
|
UPPER
|
Displays all output in upper case.
|
|
Date Functions
|
|
DATEADD
|
Adds to a specified part of a date.
|
|
DATEDIFF
|
Returns the difference between two dates.
|
|
DATEPART
|
Returns the specified part of the date requested.
|
|
DAY
|
Extracts a day from a date.
|
|
GEtdATE
|
Returns the current system date and time.
|
|
MONTH
|
Extracts the month from a date.
|
|
SET DATEFORMAT
|
Changes the format in which SQL Server reads in dates.
|
|
YEAR
|
Extracts the year from a date.
|
|
Conversion Functions
|
|
CAST
|
Changes a data type of a column in a result set.
|
|
CONVERT
|
Explicitly converts to a given data type in a result set.
|
|
Other Functions
|
|
DISTINCT
|
Omits rows that contain duplicate data.
|
|
PERCENT
|
Return a certain percentage of records that fall at the top of a range
specified.
|
|
TOP
|
Returns a specified number of records from the top of a result set.
|
