IMPORTANT: No additional bug fixes or documentation updates
will be released for this version. For the latest information, see the
current release documentation.
SHOW FUNCTIONS
edit
IMPORTANT: This documentation is no longer updated. Refer to Elastic's version policy and the latest documentation.
SHOW FUNCTIONS
editSynopsis.
DescriptionList all the SQL functions and their type. The LIKE clause can be used to restrict the list of names to the given pattern.
SHOW FUNCTIONS;
name | type
-----------------+---------------
AVG |AGGREGATE
COUNT |AGGREGATE
FIRST |AGGREGATE
FIRST_VALUE |AGGREGATE
LAST |AGGREGATE
LAST_VALUE |AGGREGATE
MAX |AGGREGATE
MIN |AGGREGATE
SUM |AGGREGATE
KURTOSIS |AGGREGATE
MAD |AGGREGATE
PERCENTILE |AGGREGATE
PERCENTILE_RANK |AGGREGATE
SKEWNESS |AGGREGATE
STDDEV_POP |AGGREGATE
SUM_OF_SQUARES |AGGREGATE
VAR_POP |AGGREGATE
HISTOGRAM |GROUPING
CASE |CONDITIONAL
COALESCE |CONDITIONAL
GREATEST |CONDITIONAL
IFNULL |CONDITIONAL
IIF |CONDITIONAL
ISNULL |CONDITIONAL
LEAST |CONDITIONAL
NULLIF |CONDITIONAL
NVL |CONDITIONAL
CURDATE |SCALAR
CURRENT_DATE |SCALAR
CURRENT_TIME |SCALAR
CURRENT_TIMESTAMP|SCALAR
CURTIME |SCALAR
DATEADD |SCALAR
DATEDIFF |SCALAR
DATEPART |SCALAR
DATETRUNC |SCALAR
DATE_ADD |SCALAR
DATE_DIFF |SCALAR
DATE_PART |SCALAR
DATE_TRUNC |SCALAR
DAY |SCALAR
DAYNAME |SCALAR
DAYOFMONTH |SCALAR
DAYOFWEEK |SCALAR
DAYOFYEAR |SCALAR
DAY_NAME |SCALAR
DAY_OF_MONTH |SCALAR
DAY_OF_WEEK |SCALAR
DAY_OF_YEAR |SCALAR
DOM |SCALAR
DOW |SCALAR
DOY |SCALAR
HOUR |SCALAR
HOUR_OF_DAY |SCALAR
IDOW |SCALAR
ISODAYOFWEEK |SCALAR
ISODOW |SCALAR
ISOWEEK |SCALAR
ISOWEEKOFYEAR |SCALAR
ISO_DAY_OF_WEEK |SCALAR
ISO_WEEK_OF_YEAR |SCALAR
IW |SCALAR
IWOY |SCALAR
MINUTE |SCALAR
MINUTE_OF_DAY |SCALAR
MINUTE_OF_HOUR |SCALAR
MONTH |SCALAR
MONTHNAME |SCALAR
MONTH_NAME |SCALAR
MONTH_OF_YEAR |SCALAR
NOW |SCALAR
QUARTER |SCALAR
SECOND |SCALAR
SECOND_OF_MINUTE |SCALAR
TIMESTAMPADD |SCALAR
TIMESTAMPDIFF |SCALAR
TIMESTAMP_ADD |SCALAR
TIMESTAMP_DIFF |SCALAR
TODAY |SCALAR
WEEK |SCALAR
WEEK_OF_YEAR |SCALAR
YEAR |SCALAR
ABS |SCALAR
ACOS |SCALAR
ASIN |SCALAR
ATAN |SCALAR
ATAN2 |SCALAR
CBRT |SCALAR
CEIL |SCALAR
CEILING |SCALAR
COS |SCALAR
COSH |SCALAR
COT |SCALAR
DEGREES |SCALAR
E |SCALAR
EXP |SCALAR
EXPM1 |SCALAR
FLOOR |SCALAR
LOG |SCALAR
LOG10 |SCALAR
MOD |SCALAR
PI |SCALAR
POWER |SCALAR
RADIANS |SCALAR
RAND |SCALAR
RANDOM |SCALAR
ROUND |SCALAR
SIGN |SCALAR
SIGNUM |SCALAR
SIN |SCALAR
SINH |SCALAR
SQRT |SCALAR
TAN |SCALAR
TRUNCATE |SCALAR
ASCII |SCALAR
BIT_LENGTH |SCALAR
CHAR |SCALAR
CHARACTER_LENGTH |SCALAR
CHAR_LENGTH |SCALAR
CONCAT |SCALAR
INSERT |SCALAR
LCASE |SCALAR
LEFT |SCALAR
LENGTH |SCALAR
LOCATE |SCALAR
LTRIM |SCALAR
OCTET_LENGTH |SCALAR
POSITION |SCALAR
REPEAT |SCALAR
REPLACE |SCALAR
RIGHT |SCALAR
RTRIM |SCALAR
SPACE |SCALAR
SUBSTRING |SCALAR
UCASE |SCALAR
CAST |SCALAR
CONVERT |SCALAR
DATABASE |SCALAR
USER |SCALAR
ST_ASTEXT |SCALAR
ST_ASWKT |SCALAR
ST_DISTANCE |SCALAR
ST_GEOMETRYTYPE |SCALAR
ST_GEOMFROMTEXT |SCALAR
ST_WKTTOSQL |SCALAR
ST_X |SCALAR
ST_Y |SCALAR
ST_Z |SCALAR
SCORE |SCORE
The list of functions returned can be customized based on the pattern.
It can be an exact match:
SHOW FUNCTIONS LIKE 'ABS';
name | type
---------------+---------------
ABS |SCALAR
A wildcard for exactly one character:
SHOW FUNCTIONS LIKE 'A__';
name | type
---------------+---------------
AVG |AGGREGATE
ABS |SCALAR
A wildcard matching zero or more characters:
SHOW FUNCTIONS LIKE 'A%';
name | type
---------------+---------------
AVG |AGGREGATE
ABS |SCALAR
ACOS |SCALAR
ASIN |SCALAR
ATAN |SCALAR
ATAN2 |SCALAR
ASCII |SCALAR
Or of course, a variation of the above:
SHOW FUNCTIONS LIKE '%DAY%';
name | type
---------------+---------------
DAY |SCALAR
DAYNAME |SCALAR
DAYOFMONTH |SCALAR
DAYOFWEEK |SCALAR
DAYOFYEAR |SCALAR
DAY_NAME |SCALAR
DAY_OF_MONTH |SCALAR
DAY_OF_WEEK |SCALAR
DAY_OF_YEAR |SCALAR
HOUR_OF_DAY |SCALAR
ISODAYOFWEEK |SCALAR
ISO_DAY_OF_WEEK|SCALAR
MINUTE_OF_DAY |SCALAR
TODAY |SCALAR