0001 ---
0002 layout: global
0003 title: SHOW FUNCTIONS
0004 displayTitle: SHOW FUNCTIONS
0005 license: |
0006 Licensed to the Apache Software Foundation (ASF) under one or more
0007 contributor license agreements. See the NOTICE file distributed with
0008 this work for additional information regarding copyright ownership.
0009 The ASF licenses this file to You under the Apache License, Version 2.0
0010 (the "License"); you may not use this file except in compliance with
0011 the License. You may obtain a copy of the License at
0012
0013 http://www.apache.org/licenses/LICENSE-2.0
0014
0015 Unless required by applicable law or agreed to in writing, software
0016 distributed under the License is distributed on an "AS IS" BASIS,
0017 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0018 See the License for the specific language governing permissions and
0019 limitations under the License.
0020 ---
0021
0022 ### Description
0023
0024 Returns the list of functions after applying an optional regex pattern.
0025 Given number of functions supported by Spark is quite large, this statement
0026 in conjunction with [describe function](sql-ref-syntax-aux-describe-function.html)
0027 may be used to quickly find the function and understand its usage. The `LIKE`
0028 clause is optional and supported only for compatibility with other systems.
0029
0030 ### Syntax
0031
0032 ```sql
0033 SHOW [ function_kind ] FUNCTIONS [ [ LIKE ] { function_name | regex_pattern } ]
0034 ```
0035
0036 ### Parameters
0037
0038 * **function_kind**
0039
0040 Specifies the name space of the function to be searched upon. The valid name spaces are :
0041
0042 * **USER** - Looks up the function(s) among the user defined functions.
0043 * **SYSTEM** - Looks up the function(s) among the system defined functions.
0044 * **ALL** - Looks up the function(s) among both user and system defined functions.
0045
0046 * **function_name**
0047
0048 Specifies a name of an existing function in the system. The function name may be
0049 optionally qualified with a database name. If `function_name` is qualified with
0050 a database then the function is resolved from the user specified database, otherwise
0051 it is resolved from the current database.
0052
0053 **Syntax:** `[ database_name. ] function_name`
0054
0055 * **regex_pattern**
0056
0057 Specifies a regular expression pattern that is used to filter the results of the
0058 statement.
0059
0060 * Except for `*` and `|` character, the pattern works like a regular expression.
0061 * `*` alone matches 0 or more characters and `|` is used to separate multiple different regular expressions,
0062 any of which can match.
0063 * The leading and trailing blanks are trimmed in the input pattern before processing. The pattern match is case-insensitive.
0064
0065 ### Examples
0066
0067 ```sql
0068 -- List a system function `trim` by searching both user defined and system
0069 -- defined functions.
0070 SHOW FUNCTIONS trim;
0071 +--------+
0072 |function|
0073 +--------+
0074 | trim|
0075 +--------+
0076
0077 -- List a system function `concat` by searching system defined functions.
0078 SHOW SYSTEM FUNCTIONS concat;
0079 +--------+
0080 |function|
0081 +--------+
0082 | concat|
0083 +--------+
0084
0085 -- List a qualified function `max` from database `salesdb`.
0086 SHOW SYSTEM FUNCTIONS salesdb.max;
0087 +--------+
0088 |function|
0089 +--------+
0090 | max|
0091 +--------+
0092
0093 -- List all functions starting with `t`
0094 SHOW FUNCTIONS LIKE 't*';
0095 +-----------------+
0096 | function|
0097 +-----------------+
0098 | tan|
0099 | tanh|
0100 | timestamp|
0101 | tinyint|
0102 | to_csv|
0103 | to_date|
0104 | to_json|
0105 | to_timestamp|
0106 |to_unix_timestamp|
0107 | to_utc_timestamp|
0108 | transform|
0109 | transform_keys|
0110 | transform_values|
0111 | translate|
0112 | trim|
0113 | trunc|
0114 | typeof|
0115 +-----------------+
0116
0117 -- List all functions starting with `yea` or `windo`
0118 SHOW FUNCTIONS LIKE 'yea*|windo*';
0119 +--------+
0120 |function|
0121 +--------+
0122 | window|
0123 | year|
0124 +--------+
0125
0126 -- Use normal regex pattern to list function names that has 4 characters
0127 -- with `t` as the starting character.
0128 SHOW FUNCTIONS LIKE 't[a-z][a-z][a-z]';
0129 +--------+
0130 |function|
0131 +--------+
0132 | tanh|
0133 | trim|
0134 +--------+
0135 ```
0136
0137 ### Related Statements
0138
0139 * [DESCRIBE FUNCTION](sql-ref-syntax-aux-describe-function.html)
