Learn
SQL Server function reference
113 functions from the Microsoft SQL documentation, each with 174 verified examples between them. Every result on every page came out of SQL Server 16.0.4265.3 rather than being copied from the documentation.
Each page also lists the versions and services that actually have the function — SQL Server 2017 or 2022, Azure SQL Database, Managed Instance, Synapse, Fabric. “Does this work on Azure?” is the question a T-SQL reference should answer without being read between the lines.
9 further functions are written and verified and go up next. Some chapters will not: where we have nothing to add to the documentation, a page here would not be worth landing on.
/
String Functions29
ASCII()Returns the ASCII code value of the leftmost character of a character expression.CHAR()Returns the single-byte character with the specified integer code, as defined by the character set and encoding of the default collation of the current database.CHARINDEX()Transact-SQL reference for the CHARINDEX function.CONCAT()This function returns a string resulting from the concatenation, or joining, of two or more string values in an end-to-end manner.CONCAT_WS()This function returns a string resulting from the concatenation, or joining, of two or more string values in an end-to-end manner, using a string separator.DIFFERENCE()DIFFERENCE returns an integer value measuring the difference between the SOUNDEX values of two different character expressions.LEFT()Returns the left part of a character string with the specified number of characters.LEN()LEN returns the number of characters of the specified string expression, excluding trailing spaces.LOWER()Returns a character expression after converting uppercase character data to lowercase.LTRIM()LTRIM returns a character string after truncating leading characters.NCHAR()Returns the Unicode character with the specified integer code, as defined by the Unicode standard.PATINDEX()PATINDEX returns the starting position of the first occurrence of a pattern in a specified expression, or zero.QUOTENAME()QUOTENAME returns a Unicode string with the delimiters added to make the input string a valid SQL Engine delimited identifier.REPLACE()Transact-SQL reference for the REPLACE function, which replaces all occurrences of a specified string value with another string value.REPLICATE()Repeats a string value a specified number of times.REVERSE()Returns the reverse order of a string value.RIGHT()Returns the right part of a character string with the specified number of characters.RTRIM()The RTRIM Transact-SQL function returns a character string after truncating all trailing spaces.SOUNDEX()SOUNDEX returns a four-character code to evaluate the similarity of two strings.SPACE()Returns a string of repeated spaces.STR()The STR Transact-SQL function returns character data converted from numeric data.STRING_ESCAPE()The STRING_ESCAPE Transact-SQL function escapes special characters in texts and returns text with escaped characters.STRING_SPLIT()This table-valued function splits a string into substrings based on a character delimiter.STUFF()The STUFF function inserts a string into another string.SUBSTRING()The SUBSTRING function returns a portion of a specified character, binary, text, or image expression.TRANSLATE()Returns the string provided as a first argument, after some characters specified in the second argument are translated into a destination set of characters, specified in the third argument.TRIM()Removes the space character or other specified characters from the start and end of a string.UNICODE()Returns the integer value, as defined by the Unicode standard, for the first character of the input expression.UPPER()Returns a character expression with lowercase character data converted to uppercase.
Mathematical Functions15
ABS()A mathematical function that returns the absolute (positive) value of the specified numeric expression. (`ABS` changes negative values to positive values. `ABS` has no effect on zero or positive values.)ASIN()A function that returns the angle, in radians, whose sine is the specified float expression. This is also called arcsine.ATAN()A function that returns the angle, in radians, whose tangent is a specified float expression. This is also called arctangent.CEILING()CEILING returns the smallest integer greater than or equal to the specified numeric expression.COS()A mathematical function that returns the trigonometric cosine of the specified angle - measured in radians - in the specified expression.EXP()Returns the exponential value of the specified float expression.FLOOR()FLOOR returns the largest integer less than or equal to the specified numeric expression.LOG()Returns the natural logarithm of the specified float expression in SQL Server.LOG10()Returns the base-10 logarithm of the specified float expression.POWER()Returns the value of the specified expression to the specified power.ROUND()ROUND returns a numeric value, rounded to the specified length or precision.SIGN()Returns the positive (+1), zero (0), or negative (-1) sign of the specified expression.SIN()Returns the trigonometric sine of the specified angle, in radians, and in an approximate numeric, float, expression.SQRT()Returns the square root of the specified float value.TAN()Returns the tangent of the input expression.
Aggregate Functions15
APPROX_COUNT_DISTINCT()This function returns the approximate number of unique non-null values in a group.AVG()This function returns the average of the values in a group. It ignores null values.CHECKSUM_AGG()This function returns the checksum of the values in a group. `CHECKSUM_AGG` ignores null values. The OVER clause can follow `CHECKSUM_AGG`.COUNT()This function returns the number of items found in a group. `COUNT` operates like the COUNT_BIG function. These functions differ only in the data types of their return values. `COUNT` always returns an int data type value. `COUNT_BIG` always returns a bigint data type value.COUNT_BIG()This function returns the number of items found in a group. `COUNT_BIG` operates like the COUNT function. These functions differ only in the data types of their return values. `COUNT_BIG` always returns a bigint data type value. `COUNT` always returns an int data type value.GROUPING()Indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 for aggregated or 0 for not aggregated in the result set. GROUPING can be used only in the SELECT \<select> list, HAVING, and ORDER BY clauses when GROUP BY is specified.GROUPING_ID()GROUPING_ID is a function that computes the level of grouping, in a SELECT, HAVING, or ORDER BY clause.MAX()MAX returns the maximum of all values of the specified expression in a group.MIN()MIN returns the minimum of all values of the specified expression in a group.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.STRING_AGG()STRING_AGG concatenates the values of string expressions and places separator values between them.SUM()SUM returns the sum of all the values, or only the DISTINCT values, in the expression.VAR()Returns the statistical variance of all values in the specified expression. May be followed by the OVER clause.VARP()Returns the statistical variance for the population for all values in the specified expression.
Date and Time Functions14
CAST and CONVERT()Reference for the CAST and CONVERT Transact-SQL functions. These functions convert expressions from one data type to another.DATEDIFF()Returns the numerical difference between a start and end date based on datepart.DATEFROMPARTS()This function returns a date value that maps to the specified year, month, and day values.DATENAME()This function returns a character string representing the specified *datepart* of the specified *date*.DATEPART()This function returns an integer corresponding to the datepart of a specified date.DATETIME2FROMPARTS()This function returns a datetime2 value for the specified date and time arguments. The returned value has a precision specified by the precision argument.DATETIMEFROMPARTS()DATETIMEFROMPARTS returns a datetime value for the specified date and time arguments.DATETIMEOFFSETFROMPARTS()Returns a datetimeoffset value for the specified date and time arguments. The returned value has a precision specified by the precision argument, and an offset as specified by the offset arguments.DAY()This function returns an integer that represents the day (day of the month) of the specified *date*.FORMAT()The FORMAT function returns a value formatted with the specified format and optional culture.MONTH()Returns an integer that represents the month of the specified *date*.SMALLDATETIMEFROMPARTS()Returns a smalldatetime value for the specified date and time.TIMEFROMPARTS()Returns a time value for the specified time and with the specified precision.YEAR()Returns an integer that represents the year of the specified *date*.
Metadata Functions11
DATABASEPROPERTYEX()DATABASEPROPERTYEX returns the current setting of the specified database option or property.DB_ID()This function returns the database identification (ID) number of a specified database.DB_NAME()The DB_NAME function returns the name of a specified database.FILE_IDEX()This function returns the file identification (ID) number for the specified logical name of a data, log, or full-text file of the current database.FULLTEXTSERVICEPROPERTY()Returns information related to the properties of the Full-Text Engine. These properties can be set and retrieved by using sp_fulltext_service.INDEXPROPERTY()Returns the named property value of a specified table identification number, index or statistics name, and property name.OBJECT_ID()OBJECT_ID returns the database object identification number of a schema-scoped object.OBJECT_SCHEMA_NAME()Returns the database schema name for schema-scoped objects. For a list of schema-scoped objects, see sys.objects (Transact-SQL).SCHEMA_ID()SCHEMA_ID returns the schema ID associated with a schema name.SCHEMA_NAME()Returns the schema name associated with a schema ID.TYPEPROPERTY()Returns information about a data type.
Analytic Functions8
CUME_DIST()For SQL Server, this function calculates the cumulative distribution of a value within a group of values. In other words, `CUME_DIST` calculates the relative position of a specified value in a group of values. Assuming ascending ordering, the `CUME_DIST` of a value in row _r_ is defined as the number of rows with values less than or equal to that value in row _r_, divided by the number of rows evaluated in the partition or query result set. `CUME_DIST` is similar to the `PERCENT_RANK` function.FIRST_VALUE()Returns the first value in an ordered set of values.LAG()Accesses data from a previous row in the same result set without the use of a self-join starting with. LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.LAST_VALUE()Returns the last value in an ordered set of values.LEAD()LEAD accesses data from a subsequent row in the same result set without the use of a self-join.PERCENT_RANK()PERCENT_RANK calculates the relative rank of a row within a group of rows in the SQL Server Database Engine.PERCENTILE_CONT()PERCENTILE_CONT calculates a percentile based on a continuous distribution of the column value.PERCENTILE_DISC()PERCENTILE_DISC computes a specific percentile for sorted values in an entire rowset or within a rowset's distinct partitions.
Security Functions8
HAS_PERMS_BY_NAME()Evaluates the effective permission of the current user on a securable. A related function is fn_my_permissions.IS_SRVROLEMEMBER()Indicates whether a SQL Server login is a member of the specified server role.SESSION_USER()SESSION_USER returns the user name of the current context in the current database.SUSER_ID()Returns the login identification number of the user.SUSER_NAME()SUSER_NAME returns the login identification name of the user.SUSER_SID()SUSER_SID returns the security identification number (SID) for the specified login name.SYSTEM_USER()Allows a system-supplied value for the current login to be inserted into a table when no default value is specified.USER_NAME()USER_NAME returns a database user name from a specified identification number, or the current user name.
Ranking Functions4
DENSE_RANK()This function returns the rank of each row within a result set partition, with no gaps in the ranking values. The rank of a specific row is one plus the number of distinct rank values that come before that specific row.NTILE()The NTILE ranking function distributes the rows in an ordered partition into a specified number of groups.RANK()RANK returns the rank of each row within the partition of a result set.ROW_NUMBER()This function numbers the output of a result set.
JSON Functions3
System Functions2
CONNECTIONPROPERTY()For a request that comes in to the server, this function returns information about the connection properties of the unique connection which supports that request.FORMATMESSAGE()Constructs a message from an existing message in sys.messages or from a provided string. The functionality of FORMATMESSAGE resembles that of the RAISERROR statement. However, RAISERROR prints the message immediately, while FORMATMESSAGE returns the formatted message for further processing.
Cryptographic Functions2
Conversion Functions2
Want to run this against your own SQL Server server from an iPhone, iPad or Mac? These are ours — every connection goes straight from the device to your database, with no account and no server of ours in between.
