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. |