SHOW FUNCTIONS

  • Article

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns the list of functions after applying an optional regex pattern. Databricks SQL supports a large number of functions. You can use SHOW FUNCTIONS in conjunction with describe function to quickly find a function and learn how to use it. The LIKE clause is optional, and ensures compatibility with other systems.

Syntax

SHOW [ function_kind ] FUNCTIONS [ { FROM | IN } schema_name ]
                                 [ [ LIKE ] { function_name | regex_pattern } ]

function_kind
  { USER | SYSTEM | ALL }

Parameters

  • function_kind

    The name space of the function to be searched upon. The valid name spaces are:

    • USER - Looks up the function(s) among the user defined functions.
    • SYSTEM - Looks up the function(s) among the system defined functions.
    • ALL - Looks up the function(s) among both user and system defined functions.

  • schema_name

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 10.4 LTS and above

    Specifies the schema in which functions are to be listed.

  • function_name

    A name of an existing function in the system. If schema_name is not provided the function name may be qualified with a schema name instead. If function_name is not qualified and schema_name has not been specified the function is resolved from the current schema.

  • regex_pattern

    A regular expression pattern that is used to filter the results of the statement.

    • Except for * and | character, the pattern works like a regular expression.
    • * alone matches 0 or more characters and | is used to separate multiple different regular expressions, any of which can match.
    • The leading and trailing blanks are trimmed in the input pattern before processing. The pattern match is case-insensitive.

Examples

-- List a system function `trim` by searching both user defined and system
-- defined functions.
> SHOW FUNCTIONS trim;
     trim

-- List a system function `concat` by searching system defined functions.
> SHOW SYSTEM FUNCTIONS concat;
   concat

-- List a qualified function `max` from schema `salesdb`.
> SHOW SYSTEM FUNCTIONS IN salesdb max;
     max

-- List all functions starting with `t`
> SHOW FUNCTIONS LIKE 't*';
               tan
              tanh
         timestamp
           tinyint
            to_csv
           to_date
           to_json
      to_timestamp
 to_unix_timestamp
  to_utc_timestamp
         transform
    transform_keys
  transform_values
         translate
              trim
             trunc
            typeof

-- List all functions starting with `yea` or `windo`
> SHOW FUNCTIONS LIKE 'yea*|windo*';
   window
     year

-- Use normal regex pattern to list function names that has 4 characters
-- with `t` as the starting character.
> SHOW FUNCTIONS LIKE 't[a-z][a-z][a-z]';
     tanh
     trim