SQL Tutorial SQL Advanced SQL Database SQL References

SQL Server Functions



SQL Server (Transact-SQL) has many built-in functions. For easy reference, a list of all SQL Server (Transact-SQL) functions are provided. These functions can be used in queries in SQL Server (Transact-SQL) and can also be used within the programming environment provided by the SQL Server (Transact-SQL) database, such as stored procedures, functions, triggers, etc.

Below is the list of SQL Server (Transact-SQL) functions, sorted by category:

SQL Server String Functions

FunctionDescription
ASCII()Returns the ASCII value for the specific character
CHAR()Returns the character based on the ASCII code
CHARINDEX()Returns the position of first occurrence of a given substring in a string
CONCAT()Adds two or more strings together
Concatenate with +Adds two or more strings together
CONCAT_WS()Adds two or more strings together with a separator
DATALENGTH()Returns the number of bytes used to represent an expression
DIFFERENCE()Compares two SOUNDEX values, and returns an integer value
FORMAT()Formats a value with the specified format
LEFT()Extracts a number of characters from a string (starting from left)
LEN()Returns the length of a string
LOWER()Converts a string to lower-case
LTRIM()Removes leading spaces from a string
NCHAR()Returns the Unicode character based on the number code
PATINDEX()Returns the position of a pattern in a string
QUOTENAME()Returns a Unicode string with delimiters added to make the string a valid SQL Server delimited identifier
REPLACE()Replaces all occurrences of a substring within a string, with a new substring
REPLICATE()Repeats a string a specified number of times
REVERSE()Reverses a string and returns the result
RIGHT()Extracts a number of characters from a string (starting from right)
RTRIM()Removes trailing spaces from a string
SOUNDEX()Returns a four-character code to evaluate the similarity of two strings
SPACE()Returns a string of the specified number of space characters
STR()Returns a number as string
STUFF()Deletes a part of a string and then inserts another part into the string, starting at a specified position
SUBSTRING()Extracts some characters from a string
TRANSLATE()Replaces a sequence of characters in a string with another set of characters.
TRIM()Removes leading and trailing spaces (or other specified characters) from a string
UNICODE()Returns the Unicode value for the first character of the input expression
UPPER()Converts a string to upper-case

SQL Server Math/Numeric Functions

FunctionDescription
ABS()Returns the absolute value of a number
ACOS()Returns the arc cosine of a number
ASIN()Returns the arc sine of a number
ATAN()Returns the arc tangent of a number
ATN2()Returns the arc tangent of two numbers
CEILING()Returns the smallest integer value that is >= a number
COS()Returns the trigonometric cosine of an angle
COT()Returns the trigonometric cotangent of an angle
DEGREES()Converts a value in radians to degrees
EXP()Returns e raised to the power of a specified number
FLOOR()Returns the largest integer value that is <= to a number
LOG()Returns the natural logarithm of a number, or the logarithm of a number to a specified base
LOG10()Returns the logarithm of a number to base 10
PI()Returns the value of PI
POWER()Returns the value of a number raised to the power of another number
RADIANS()Converts a degree value into radians
RAND()Returns a random number from 0 through 1, exclusive
ROUND()Rounds a number to a specified number of decimal places
SIGN()Returns the sign of a number
SIN()Returns the trigonometric sine of an angle
SQRT()Returns the square root of a number
SQUARE()Returns the square of a number
TAN()Returns the trigonometric tangent of an angle

SQL Server Aggregate Functions

FunctionDescription
AVG()Returns the average value of an expression
COUNT()Returns the number of records returned by a select query
MAX()Returns the maximum value in a set of values
MIN()Returns the minimum value in a set of values
STDEVReturns the statistical standard deviation of all values in the specified expression
STDEVPReturns the statistical standard deviation for the population for all values in the specified expression
SUM()Calculates the sum of a set of values
VARReturns the statistical variance of all values in the specified expression
VARPReturns the statistical variance for the population for all values in the specified expression

SQL Server Date/Time Functions

FunctionDescription
CURRENT_TIMESTAMPReturns the current date and time
DATEADD()Adds a time/date interval to a date and then returns the date
DATEDIFF()Returns the difference between two dates
DATEFROMPARTS()Returns a date from the specified parts (year, month, and day values)
DATENAME()Returns a specified part of a date (as string)
DATEPART()Returns a specified part of a date (as integer)
DATETIME2FROMPARTS()Returns a datetime value from the specified date and time arguments with specified precision
DATETIMEFROMPARTS()Returns a datetime value from the specified date and time arguments
DAY()Returns the day of the month for a specified date
GETDATE()Returns the current database system date and time
GETUTCDATE()Returns the current database system UTC date and time
ISDATE()Checks an expression and returns 1 if it is a valid date, otherwise 0
MONTH()Returns the month part for a specified date (a number from 1 to 12)
SYSDATETIME()Returns the date and time of the SQL Server
SYSUTCDATETIME()Returns the date and time of the SQL Server as UTC time
TIMEFROMPARTS()Returns a time value from the specified time and with the specified precision
YEAR()Returns the year part for a specified date

SQL Server Advanced Functions

FunctionDescription
@@VERSIONReturns system and build information for the current installation of SQL Server
CASE()Goes through conditions and return a value when the first condition is met
CAST()Converts a value (of any type) into a specified datatype
CHOOSE()Returns item at specified index number
COALESCE()Returns the first non-null value in a list
CONVERT()Converts a value (of any type) into a specified datatype
CURRENT_USERReturns the name of the current user in the SQL Server database
IIF()Returns a value if a condition is TRUE, or another value if a condition is FALSE
ISNULL()Return a specified value if the expression is NULL, otherwise return the expression
ISNUMERIC()Tests whether an expression is numeric
NULLIF()Returns NULL if two expressions are equal
SESSION_USERReturns the name of the current user in the SQL Server database
SESSIONPROPERTY()Returns the session settings for a specified option
SYSTEM_USERReturns the login name for the current user
TRY_CAST()Tries to convert a value (of any type) into a specified datatype
TRY_CONVERT()Tries to convert a value (of any type) into a specified datatype
USER_NAME()Returns the database user name based on the specified id