SQL Server Built-in Functions

The following is the list of built-in String functions, DateTime functions, Numeric functions and conversion functions.

String Functions

Function Description
ASCII Returns the ASCII code value for the leftmost character of a character expression.
CHAR Returns a character for an ASCII value.
CHARINDEX Searches for one character expression within another character expression and returns the starting position of the first expression.
CONCAT Concatenates two or more string values in an end to end manner and returns a single string.
LEFT Returns a given number of characters from a character string starting from the left
LEN Returns a specified number of characters from a character string.
LOWER Converts a string to lower case.
LTRIM Removes all the leading blanks from a character string.
NCHAR Returns the Unicode character with the specified integer code, as defined by the Unicode standard.
PATINDEX Returns the starting position of the first occurrence of the pattern in a given string.
REPLACE Replaces all occurrences of a specified string with another string value.
RIGHT Returns the right part of a string with the specified number of characters.
RTRIM Returns a string after truncating all trailing spaces.
SPACE Returns a string of repeated spaces.
STR Returns character data converted from numeric data. The character data is right justified, with a specified length and decimal precision.
STUFF Inserts a string into another string. It deletes a specified length of characters from the first string at the start position and then inserts the second string into the first string at the start position.
SUBSTRING Returns part of a character, binary, text, or image expression
UPPER Converts a lowercase string to uppercase.

DateTime Functions

Function Description
CURRENT_TIMESTAMP Returns the current system date and time of the computer on which the SQL server instance is installed. Time zone is not included.
DATEADD Returns a new datetime value by adding an interval to the specified datepart of the specified date
DATEDIFF Returns the difference in datepart between two given dates.
DATENAME Returns a datepart as a character string.
DATEPART Returns a datepart as an integer
DAY Returns the Day as an integer representing the Day part of a specified date.
GETDATE Returns a datetime value containing the date and time of the computer on which the SQL Server instance is installed. It does not include the time zone.
GETUTCDATE Returns a datetime value in UTC format (Coordinated Universal Time), containing the date and time of the computer on which the SQL Server instance is installed.
MONTH Returns the Month as an integer representing the Month part of a specified date.
YEAR Returns the Year as an integer representing the Year part of a specified date.
ISDATE Determines whether the input is a valid date, time or datetime value.

Numeric Functions

Function Description
ABS Returns the absolute value of a number.
AVG Returns the average value of an expression/column values.
CEILING Returns the nearest integer value which is larger than or equal to the specified decimal value.
COUNT Returns the number of records in the SELECT query.
FLOOR Returns the largest integer value that is less than or equal to a number. The return value is of the same data type as the input parameter.
MAX Returns the maximum value in an expression.
MIN Returns the minimum value in an expression.
RAND Returns a random floating point value using an optional seed value.
ROUND Returns a numeric expression rounded to a specified number of places right of the decimal point.
SIGN Returns an indicator of the sign of the input integer expression.
SUM Returns the sum of all the values or only the distinct values, in the expression. NULL values are ignored.

Conversion Functions

Function Description
CAST Converts a value of any type to a specified data type.
CONVERT Converts and formats a value of one data type to another data type.
TRY_CAST Returns a value cast to a specified data type if the cast succeeds else returns null.
TRY_CONVERT Returns a value cast to a specified data type if the cast succeeds else returns null.

Advanced Functions

Function Description
IIF It can be used as if-else condition in a query.
ISNULL Replaces the NULL value with a given replacement expression.
ISNUMERIC Determines whether the given expression is a valid numeric type or not.
USER_NAME Returns the current logged-in user name.
Want to check how much you know SQL Server?
Start SQL Server Test