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
| Function | Description |
|---|
| 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
| 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 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
| Function | Description |
|---|
| 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 |
| STDEV | Returns the statistical standard deviation of all values in the specified expression |
| STDEVP | Returns the statistical standard deviation for the population for all values in the specified expression |
| SUM() | Calculates the sum of a set of values |
| VAR | Returns the statistical variance of all values in the specified expression |
| VARP | Returns the statistical variance for the population for all values in the specified expression |
SQL Server Date/Time Functions
| Function | Description |
|---|
| CURRENT_TIMESTAMP | Returns 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
| Function | Description |
|---|
| @@VERSION | Returns 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_USER | Returns 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_USER | Returns the name of the current user in the SQL Server database |
| SESSIONPROPERTY() | Returns the session settings for a specified option |
| SYSTEM_USER | Returns 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 |
