THE WORLD'S LARGEST WEB DEVELOPER SITE

SQL Server Functions


SQL Server has many built-in functions.

This reference contains string, numeric, date, conversion, and advanced functions in SQL Server.


SQL Server String Functions

Function Description
ASCII Returns the number code that represents the specific character
CHAR Returns the ASCII character based on the number code
CHARINDEX Returns the location of a substring in a string
CONCAT Concatenates two or more strings together
Concat with + Concatenates two or more strings together
CONCAT_WS Concatenates two or more strings together, and separates the concatenated string values with the specified delimiter
DATALENGTH Returns the length of an expression (in bytes)
DIFFERENCE Compares two SOUNDEX values, and returns an integer value
FORMAT Returns a value formatted with the specified format (and an optional culture)
LEFT Extracts a substring from a string (starting from left)
LEN Returns the length of the specified 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 location 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 a sequence of characters in a string with another set of characters
REPLICATE Repeats a string value a specified number of times
REVERSE Reverses a string and returns the result
RIGHT Extracts a substring 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 with a specified number of spaces
STR Returns a string representation of a number
STUFF Deletes a sequence of characters from a string and then inserts another sequence of characters into the string, starting at a specified position
SUBSTRING Extracts a substring from a string
TRANSLATE Returns the string from the first argument after the characters specified in the second argument are translated into the characters specified in the third argument.
TRIM Removes the space character or other specified characters from the start or end of a string
UNICODE Returns an integer value (the Unicode value), for the first character of the input expression
UPPER Converts a string to upper-case


SQL Server Math/Numeric Functions

Function Description
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 n and m
AVG Returns the average value of an expression
CEILING Returns the smallest integer value that is greater than or equal to a number
COUNT Returns the count of an expression
COS Returns the cosine of a number
COT Returns the cotangent of a number
DEGREES Converts a radian value into degrees
EXP Returns e raised to the power of number
FLOOR Returns the largest integer value that is equal to or less than a number
LOG Returns the natural logarithm of a number or the logarithm of a number to a specified base
LOG10 Returns the base-10 logarithm of a number
MAX Returns the maximum value of an expression
MIN Returns the minimum value of an expression
PI Returns the value of PI
POWER Returns m raised to the nth power
RADIANS Converts a value in degrees to radians
RAND Returns a random number or a random number within a range
ROUND Returns a number rounded to a certain number of decimal places
SIGN Returns a value indicating the sign of a number
SIN Returns the sine of a number
SQRT Returns the square root of a number
SQUARE Returns the square root of a number
SUM Returns the summed value of an expression
TAN Returns the tangent of a number

SQL Server Date Functions

Function Description
CURRENT_TIMESTAMP Returns the current date and time
DATEADD Returns a date after a certain time/date interval has been added
DATEDIFF Returns the difference between two date values, based on the interval specified
DATENAME Returns a specified part of a given date, as a string value
DATEPART Returns a specified part of a given date, as an integer value
DAY Returns the day of the month (from 1 to 31) for a given date
GETDATE Returns the current date and time
GETUTCDATE Returns the current UTC date and time
ISDATE Returns 1 if the expression is a valid date, otherwise 0
MONTH Returns the month (from 1 to 12) for a given date
YEAR Returns the year (as a four-digit number) for a given date

SQL Server Conversion Functions

Function Description
CAST Converts an expression from one data type to another
CONVERT Converts an expression from one data type to another

SQL Server Advanced Functions

Function Description
COALESCE Returns the first non-null expression in a list
CURRENT_USER Returns the name of the current user in the SQL Server database
ISNULL Lets you return an alternative value when an expression is NULL
ISNUMERIC Returns 1 if the expression is a valid number, otherwise 0
NULLIF Compares two expressions
SESSION_USER Returns the user name of the current session in the SQL Server database
SESSIONPROPERTY Returns the setting for a specified option of a session
SYSTEM_USER Returns the login name information for the current user in the SQL Server database
USER_NAME Returns the user name in the SQL Server database