Funktionen
prev
next
Inhalt
Blubb
SQL provides a wide range of functions on rows/cells to calculate additional values, but those provided by a specific system differ from implementation to implementation. MF allows the most common methods and tries to emulate some other.
| Function | Description | Limitations |
|---|---|---|
| COUNT(*) | returns the number of rows which are not null | |
| RAND() | returns a random number between 0 and 1 | |
| MIN(col) | returns the smalles value of the column | only numeric columns |
| MAX(col) | returns the bigges value of the column | only numeric columns |
| SUM(col) | returns the sum value of the column | only numeric columns |
| AVG(col) | returns the average value of the column | only numeric columns |
| ABS(cell) | returns the absulute value of the cell | only numeric columns |
| FLOOR(cell) | returns the integer x<=value of the cell | only numeric columns |
| CEIL(cell) | returns the integer x>=value of the cell | only numeric columns |
| ROUND(cell[,d]) | returns the rounded value [to d digits] of the cell | only numeric columns |
| MOD(a,b) | returns the modulo of a / b | only numeric columns |
| POWER(a,b) | returns the value of ab | only numeric columns |
| SQRT(cell) | returns the square root of the cell | only numeric columns |
| SIN(cell), COS(cell), TAN(cell) | returns the sin/cos/tan value of the cell | only numeric columns |
| ASIN(cell), ACOS(cell), ATAN(cell) | returns the arcus sin/cos/tan value of the cell | only numeric columns |
| CURRENT_DATE() | returns the current date | only date columns |
| CURRENT_TIMESTAMP() | returns the current date/time | only datetime columns |
| UPPER(cell) | returns the string in uppercase spaces | only string columns |
| LOWER(cell) | returns the string in lowercase spaces | only string columns |
| LTRIM(cell) | returns the string without leading spaces | only string columns |
| RTRIM(cell) | returns the string without tailing spaces | only string columns |
| TRIM(cell) | returns the string without leading and tailing spaces | only string columns |
| LENGTH(cell) | returns the string string length (characters in char fields, bytes in binary | only string/binary columns |
| COALESCE(val1, val2, ...) | returns the first non null value of the value list |
cell means that the function returns a individual value per dataset, column means the function returns one value per select statement, which is identical on all result datasets.
cell/col can be set to a fixed value if needed
a/b can be cells or values
d can only be a value