Functions

Several operators and functions may be used for the appropriate datatypes while performing arithmetic operations after the select clause, the where clause and so on. Following are some important SQL Server built-in functions.

Aggregate functions

Function	Result
sum([all | distinct] expression)	Total of all or distinct values in numeric column
avg([all | distinct] expression)	Average of all or distinct values in numeric column
count([all | distinct] expression)	Number of non-null values in column
count(*)	Number of selected rows
max(expression)	Highest value of expression or column
min(expression)	Lowest value of expression or column

Datatype conversion functions

Function	Argument	Result
convert	(datatype , expression[, style])	Converts between datatypes; reformats date/time and money data for display.

Date functions

Function	Argument	Result
dateadd	(datepart, numeric_expression, date)	adds the specified number of the specified date parts (such as month, day etc.) to the date.
datediff	(datepart, date1, date2)	Returns date2 - date1 in the specified date part.
datename	(datepart, date)	Returns the name of the specified date part (such as the month "August") of a datetime value, as a character string. If the result is numeric, such as "12" for the day a character string containing the number is returned.
datepart	(datepart, date)	Returns integer value of specified part.
getdate	( )	Returns the current system date and time.

Mathematical functions

Function	Argument	Result
abs	(numeric)	Returns the absolute value of a given expression
acos	(numeric)	Returns the angle whose cosine is the specified value.
asin	(numeric)	Returns the angle whose sine is the specified value.
atan	(numeric)	Returns the angle whose tangent is the specified value.
atn2	(numeric1, numeric2)	Returns the angle (in radians) whose tangent is (numeric1/numeric2).
ceiling	(numeric)	Returns the smallest integer greater than or equal to the specified value.
cos	(numeric)	Returns the cosine of the specified angle .
cot	(numeric)	Returns the cotangent of the specified angle .
degrees	(numeric)	Converts radians to degrees.
exp	(numeric)	Returns the exponential value of the specified value.
floor	(numeric)	Returns the largest integer less than or equal to the specified value.
log	(numeric)	Returns the natural logarithm of the specified value.
log10	(numeric)	Returns the base 10 logarithm of the specified value.
pi	()	Returns the constant value 3.14159...
power	(numeric, power)	Returns the value of numeric raised to the power power.
radians	(numeric)	Converts degrees to radians.
rand	([integer])	Returns a random floating point value between 0 and 1 using the optional integer as a seed.
round	(numeric, integer)	Rounds the numeric so that it has integer significant digits.
sign	(numeric)	Returns positive (+1), zero (0), or negative (-1).
sin	(numeric)	Returns the sine of the specified angle
sqrt	(numeric)	Returns the square root of the specified value.
tan	(numeric)	Returns the tangent of the specified angle

String Functions

Function	Argument	Result
ascii	(char_expr)	Returns the ASCII code for the first character in the expression.
char	(integer)	Converts a single-byte integer value to a character value
charindex	(expression1, expression2)	Searches expression2 for the first occurrence of expression1 and returns an integer representing its starting position. If expression1 is not found, returns 0. Wildcards are treated as literals.
lower	(chars)	Converts uppercase to lowercase, returning a character value.
ltrim	(chars)	Removes leading blanks from the character expression.
replace	(expression, char1, char2)	Replaces occurrences of char1 with char2 in expression
reverse	(chars)	Returns the reverse of chars; if char_expr is "xyz", "zyx" is returned.
right	(chars, integer)	Returns the part of the character expression starting the specified number of characters from the right.
rtrim	(chars)	Removes trailing blanks.
space	(integer)	Returns a string with the indicated number of spaces.
str	(numeric [, length [, decimal] ])	Returns a character representation of the floating point number. length is total number of characters to be returned; decimal is the number of decimal digits to be returned.
substring	(expression, start, length)	Returns part of a character or binary string (from position 'start'; 'length' characters are returned).
upper	(char_expr)	Converts lowercase to uppercase.

System functions

Function	Argument	Result
col_name	(object_id, column_id [, database_id])	Returns the column name.
col_length	(object_name, column_name)	Returns the defined length of column.
datalength	(expression)	Returns the actual length of expression in bytes.
db_id	([database_name])	Returns the database ID number. .
db_name	([database_id])	Returns the database name. database_id must be a numeric expression. If no database_id is supplied, db_name returns the name of the current database.
host_name	( )	Returns the current host computer name of the client process (not the Server process).
isnull	(expression1, expression2)	Substitutes expression2 for expression1 when expression1 evaluates to NULL.
object_id	(object_name)	Returns the object ID.
object_name	(object_id[, database_id])	Returns the object name.
user		Returns the user's name.
user_id	([user_name])	Returns the user's ID number from the sysusers table.
user_name	([user_id])	Returns the user's name

Following are some important examples that illustrate function use:

• SELECT subjectid, isnull(keywords, 'none')
      AS KEYWORDS from subject

  Returns 'none' if the keywords column contains a null; other wise returns keywords

• select replace(title,',',';')
      as semititle from book

  Returns the books titles with ';' substituted in places where a ',' occurs.

• select bookid,
      unitprice/100*(case
      when discount is null then 100
      else 100-discount end) as FinalPrice
      from orderdetails

  Returns the bookid and price after discount of ordered books. If discount value contains null, regular price is returned; otherwise discounted price is returned

• select title, price from book
      where floor(price) between  25 and 30

  Returns all the books with cost in range 25.00 - 30.99

SQL Server functions are most useful when called from T-SQL queries. Users may obtain data from SQL Server through select statements even when the returned data is not from a database in the following way:

select abs(sinh(12.5))
select user
select getdate()