SQL Built-In Function Reference
RDM provides many built-in functions that you can use in queries to return data or perform operations on data.
Aggregate Functions
Aggregate functions perform a calculation on a set of values and return a single value. Except for COUNT, aggregate functions ignore null values. Aggregate functions are frequently used with the GROUP BY clause of the SELECT statement.
| Function | Description |
|---|---|
| COUNT | Returns the number (distinct) of rows in the aggregate. |
| SUM | Returns the sum of the (distinct) values of expression in the aggregate. |
| AVG | Returns the average of the (distinct) values of expression in the aggregate. |
| MIN | Returns the minimum expression value in the aggregate. |
| MAX | Returns the maximum expression value in the aggregate. |
| EVERY | Returns true if cond_expr is true for every row in the aggregate. |
| ANY/SOME | Returns true if cond_expr is true for at least one row in the aggregate. |
| INVAR | Checks that the value of expressions is invariant (i.e., does not change) within each aggregate. |
| VAR_POP | Computes the population variance over each aggregate. |
| var_samp | Computes the sample variance over each aggregate. |
| stddev_pop | Computes the population standard deviation over each aggregate. |
| stddev_samp | Computes the sample standard deviation over each aggregate. |
Scalar Functions
Mathematical Functions
The following scalar functions perform a calculation, usually based on input values that are provided as arguments, and return a numeric value:
| Function | Description |
|---|---|
| abs | Returns the absolute value of an expression. |
| acos | Returns the arccosine of an expression. |
| asin | Returns the arcsine of an expression. |
| atan | Returns the arctangent of an expression. |
| atan2 | Returns the arctangent of an x-y coordinate pair. |
| ceil | ceiling | Finds the upper bound for an expression. |
| cos | Returns the cosine of an angle. |
| cot | Returns the cotangent of an angle. |
| degrees | Convert radians to degrees |
| exp | Returns the value of an exponential function. |
| floor | Finds the lower bound for an expression. |
| ln | log | Returns the natural logarithm of an expression. |
| mod | Returns the remainder of arith_expr1/arith_expr2. |
| pi | Returns the value of pi. |
| power | Raises numeric value to specified power. |
| rand | Returns next random floating-point number. Non-zero num is seed. |
| round | Returns arith_expr rounded to the nearest integer. |
| sign | Returns the sign of an expression (-1, 0, +1). |
| sin | Returns the sine of an angle. |
| sqrt | Returns the square root of an expression. |
| tan | Returns the tangent of an angle. |
Date and Time Functions
The data type DATE assumes the Gregorian calendar even for dates prior to the introduction of the Gregorian calendar. This means that databases that store historical dates prior to the introduction of the Gregorian calendar may not compute SELECT with date ranges, DAYOFWEEK, and WEEK correctly.
| Function | Description |
|---|---|
| age | Returns the age (in full years). |
| curdate | Returns the current date. |
| curtime | Returns the current time. |
| curtimestamp | Returns the current date and time |
| dayofmonth | Returns the day of the month. |
| dayofweek | Returns the day of the week. |
| dayofyear | Returns the day of the year. |
| hour | Returns the hour. |
| minute | Returns the minute. |
| month | Returns the month. |
| quarter | Returns the quarter. |
| second | Returns the second. |
| trunctime | Truncates a time or timestamp value based on a specified interval. |
| week | Returns the week. |
| year | Returns the year. |
String Functions
The following scalar functions perform an operation on a string input value and return a string or numeric value:
| Function | Description |
|---|---|
| ascii | Returns the numeric ASCII value of a character |
| char | Returns the ASCII character with numeric value num |
| character_length | Return the number of characters in a string |
| concat | Concatenates two strings |
| convert | Convert expression to a char or wchar string of maximum length width and formatted as specified in "format". |
| insstr | Replace num2 chars from string_expr2 in string_expr1 beginning at position num1 (1st position is 1 not 0) |
| lcase | Converts a string to lowercase |
| left | Returns the leftmost num characters from the string |
| length | Returns the length of the string |
| locate | Locate string_expr1 from position num in string_expr2 |
| ltrim | Removes all leading spaces from string |
| octet_length | Return the minimum value in each aggregate |
| position | Find start position of string_expr2 within string_expr1. |
| repeat | Repeats string num times |
| replace | Replace string_expr2 with string_expr3 in string_expr1 |
| right | Returns the rightmost num characters from string |
| rtrim | Removes all trailing spaces from string |
| substring | Returns num2 characters from string_expr beginning at position num1. |
| trim | If num == 1, trim leading occurrences of char from string_expr. If num == 2, trim trailing occurrences of char from string_expr. If num == 3, trim both leading and trailing occurrences of char from string_expr. |
| ucase | Convert string to uppercase |
| unicode | Returns the numeric Unicode value of a character |
| wchar | Returns a Unicode character with numeric value num. |
Miscellaneous Functions
The following functions perform operations that do not fit into any of the other categories.
| Function | Description |
|---|---|
| coalesce | Returns the first non-null expression in a list |
| if | Implement a conditional selection |
| ifnull | Retrieve an expression if another expression is null |
| last_insert_id | Return the most recently auto-generated rowid value |
| last_insert_guid | Return the most recently auto-generated guid value |
| new_guid | Return a new globally universal identifier value |
| query | Returns the scalar value returned from the specified SELECT statement string |