This page shows you a handful of built-in SQL functions including aggregate functions, string functions, control flow function, and math functions for your reference.
An SQL aggregate function summarizes a group of rows and returns a single value for each group. The aggregate functions, therefore, are often used in conjunction with the
GROUP BY clause. The
GROUP BY clause groups the rows into groups and the aggregate function calculates a summarized value for each group.
The following is the generic syntax of an aggregate function.
aggregate_function(DISTINCT | ALL expression);
ALL options change the behavior of the aggregate function. The
DISTINCT instructs the function to consider only unique values for the argument, whereas the ALL option tells the function to include all values including duplicate.
The following table shows the list aggregate functions implemented in all relational database management systems.
|Calculates the average of the set of values in a group, |
|Returns the number of rows.|
|Returns a single maximum value in a column or a group.|
|Returns a single minimum value in a column or a group.|
|Returns the sum of values in a column or a group.|
SQL provides you with some many handy functions for manipulating strings. The strings in this context include values of type
|Returns ASCII code of the first character of a string|
|Returns the character corresponding to the input ASCII code|
|Returns the result of concatenation of two or more strings|
|Converts all characters in a string to lowercase|
|Returns the number of characters in a given string. Some database systems use the |
|Extracts a substring from a string|
|Replaces all occurrences of a specified substring in a string by a new substring|
|Removes unwanted characters e.g., whitespaces from a string|
|Converts all characters in a string to uppercase|
|Returns the first non-null argument in the argument list|
|Compares two expressions and returns |
The following table shows a list of the mathematical functions commonly implemented by the RDBMS products.
|Returns the absolute value|
|Returns the arc cosine of an argument|
|Returns the arc sine of an argument|
|Returns the arc tangent of an argument|
|Rounds up a float to the nearest integer value|
|Returns the cosine of an argument|
|Returns the cotangent of an argument|
|Returns the |
|Returns the natural logarithm of the argument|
|Returns the natural logarithm of the first argument|
|Returns the base-10 logarithm of the argument|
|Returns the base-2 logarithm of the argument|
|Returns the remainder (modulo) of a number divided by another|
|Returns the value of pi which is |
|Returns a number raised to a power of a specified number|
|Returns a random floating-point value|
|Rounds a number to a specific precision|
|Returns the sign of an argument|
|Returns the sine of an argument|
|Returns the square root of an argument|
|Returns the tangent of an argument|
|Truncates to specified number of decimal places|