New

The executive guide to generative AI

Read more
About usPartnersSupport|Login
IMPORTANT: No additional bug fixes or documentation updates will be released for this version. For the latest information, see the current release documentation.
« SHOW COLUMNS SHOW TABLES »
Elastic Docs ›Elasticsearch Guide [6.6] ›SQL Access ›SQL Commands

SHOW FUNCTIONS

edit

SHOW FUNCTIONS

edit

This functionality is in beta and is subject to change. The design and code is less mature than official GA features and is being provided as-is with no warranties. Beta features are not subject to the support SLA of official GA features.

Synopsis.

SHOW FUNCTIONS [ LIKE pattern? ]?

SQL match pattern

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
MAX              |AGGREGATE
MIN              |AGGREGATE
SUM              |AGGREGATE
KURTOSIS         |AGGREGATE
PERCENTILE       |AGGREGATE
PERCENTILE_RANK  |AGGREGATE
SKEWNESS         |AGGREGATE
STDDEV_POP       |AGGREGATE
SUM_OF_SQUARES   |AGGREGATE
VAR_POP          |AGGREGATE
HISTOGRAM        |GROUPING
COALESCE         |CONDITIONAL
GREATEST         |CONDITIONAL
IFNULL           |CONDITIONAL
ISNULL           |CONDITIONAL
LEAST            |CONDITIONAL
NULLIF           |CONDITIONAL
NVL              |CONDITIONAL
CURRENT_TIMESTAMP|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
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
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
« SHOW COLUMNS SHOW TABLES »
Was this helpful?
Feedback