Summary of Functions¶
Snowflake supports most of the standard functions defined in SQL:1999, as well as parts of the SQL:2003 analytic extensions.
In this Topic:
Scalar Functions¶
Category/Sub-category |
Functions |
|---|---|
BITAND , BITNOT , BITOR , BITSHIFTLEFT , BITSHIFTRIGHT , BITXOR (see also: Bitwise Aggregation Functions) |
|
[ NOT ] BETWEEN , BOOLAND , BOOLNOT , BOOLOR , BOOLXOR , CASE , COALESCE , DECODE , EQUAL_NULL , GREATEST , IFF , IFNULL , [ NOT ] IN , IS [ NOT ] DISTINCT FROM , IS [ NOT ] NULL , IS_NULL_VALUE , LEAST , NULLIF , NVL , NVL2 , REGR_VALX , REGR_VALY , ZEROIFNULL |
|
General
|
CURRENT_* ( CLIENT | DATE | TIME | TIMESTAMP | VERSION ) , LOCALTIME , LOCALTIMESTAMP , SYSDATE |
Session
|
CURRENT_* ( ACCOUNT | ROLE | SESSION | STATEMENT | TRANSACTION | USER ) , LAST_QUERY_ID , LAST_TRANSACTION |
Session Object
|
CURRENT_* ( DATABASE | SCHEMA | SCHEMAS | WAREHOUSE ) |
RANDOM , SEQ1 / SEQ2 / SEQ4 / SEQ8 , UUID_STRING |
|
Rounding & Truncation
|
ABS , CEIL , FLOOR , MOD , ROUND , SIGN , TRUNC / TRUNCATE |
Exponent & Root
|
CBRT , EXP , POW / POWER , SQRT , SQUARE |
Logarithmic
|
LN , LOG |
Trigonometric
|
ACOS , ACOSH , ASIN , ASINH , ATAN , ATAN2 , ATANH , COS , COSH , COT , DEGREES , HAVERSINE , PI , RADIANS , SIN , SINH , TAN , TANH |
General
|
ASCII , BIT_LENGTH , CHR / CHR , CONCAT / || , CONCAT_WS , INSERT , LENGTH , LPAD , LTRIM , OCTECT_LENGTH , PARSE_IP , PARSE_URL , REPEAT , REVERSE , RPAD , RTRIM , RTRIMMED_LENGTH , SOUNDEX , SPACE , SPLIT , SPLIT_PART , SPLIT_TO_TABLE , STRTOK , STRTOK_TO_ARRAY , STRTOK_SPLIT_TO_TABLE , TRANSLATE , TRIM , UNICODE , UUID_STRING |
Case Conversion
|
INITCAP , LOWER , UPPER |
Regular Expression Matching; . (String Functions (Regular Expressions))
|
REGEXP , REGEXP_COUNT , REGEXP_INSTR , REGEXP_LIKE , REGEXP_REPLACE , REGEXP_SUBSTR , RLIKE |
Other Matching/Comparison
|
CHARINDEX , CONTAINS , EDITDISTANCE , ENDSWITH , ILIKE , ILIKE ANY, LEFT , LIKE , LIKE ALL, LIKE ANY , POSITION , REPLACE , RIGHT , STARTSWITH , SUBSTR |
Compression/Decompression
|
COMPRESS , DECOMPRESS_BINARY , DECOMPRESS_STRING |
Encode/Decode
|
BASE64_DECODE_BINARY , BASE64_DECODE_STRING , BASE64_ENCODE , HEX_DECODE_BINARY , HEX_DECODE_STRING , HEX_ENCODE , TRY_* ( decode binary and string functions ) |
Cryptographic/Checksum
|
MD5 / MD5_HEX , MD5_BINARY , MD5_NUMBER , SHA1 / SHA1_HEX , SHA1_BINARY , SHA2 / SHA2_HEX , SHA2_BINARY |
Hash (non-cryptographic)
|
HASH , HASH_AGG |
Construction
|
DATE_FROM_PARTS / DATEFROMPARTS , TIME_FROM_PARTS / TIMEFROMPARTS , TIMESTAMP_FROM_PARTS / TIMESTAMPFROMPARTS |
Extraction
|
DATE_PART , DAY , DAYNAME , DAYOFMONTH , DAYOFWEEK , DAYOFWEEKISO , DAYOFYEAR , EXTRACT , HOUR , LAST_DAY , MINUTE , MONTH , MONTHNAME , QUARTER , SECOND, WEEK , WEEKOFYEAR , WEEKISO , YEAR , YEAROFWEEK , YEAROFWEEKISO |
Addition/Subtraction
|
ADD_MONTHS , DATEADD, DATEDIFF , TIMEADD, TIMEDIFF , TIMESTAMPADD , TIMESTAMPDIFF |
Truncation
|
DATE_TRUNC , TIME_SLICE , TRUNC |
Type Conversion
|
TO_DATE , TO_TIME , TO_TIMESTAMP , TO_TIMESTAMP_* ( LTZ | NTZ | TZ ) |
Time Zone
|
CONVERT_TIMEZONE |
Parsing
|
ARRAYS_OVERLAP , CHECK_JSON , CHECK_XML , JSON_EXTRACT_PATH_TEXT, PARSE_JSON , PARSE_XML , STRIP_NULL_VALUE |
Array & Object
|
ARRAY_AGG , ARRAY_APPEND , ARRAY_CAT , ARRAY_COMPACT , ARRAY_CONSTRUCT , ARRAY_CONSTRUCT_COMPACT , ARRAY_CONTAINS , ARRAY_INSERT , ARRAY_POSITION , ARRAY_PREPEND , ARRAY_SIZE , ARRAY_SLICE , ARRAY_TO_STRING , OBJECT_AGG , OBJECT_CONSTRUCT , OBJECT_INSERT , OBJECT_DELETE |
Data Extraction
|
FLATTEN , GET , GET_PATH , XMLGET |
Casts
|
AS_* (all data types) , TO_ARRAY , TO_JSON , TO_OBJECT , TO_VARIANT, TO_XML |
Type Predicates
|
IS_* (all data types) , TYPEOF |
CAST , TO_* (all supported Snowflake data types) , TRY_CAST , TRY_TO_* ( numeric, Boolean, date & time data types ) |
|
Utility
|
GET_DDL , HASH |
Aggregate Functions¶
Category/Sub-category |
Functions |
|---|---|
General
|
ANY_VALUE , AVG , CORR , COUNT , COVAR_POP , COVAR_SAMP , HASH_AGG , LISTAGG , MAX , MEDIAN , MIN , MODE , PERCENTILE_CONT , PERCENTILE_DISC , STDDEV / STDDEV_POP , STDDEV_SAMP , SUM , VAR_POP / VARIANCE_POP , VAR_SAMP / VARIANCE_SAMP / VARIANCE |
Order-sensitive . (Window Functions)
|
CUME_DIST , DENSE_RANK , FIRST_VALUE , LAG , LAST_VALUE , LEAD , NTH_VALUE , NTILE , PERCENT_RANK , RANK , ROW_NUMBER , WIDTH_BUCKET |
Bitwise
|
BITAND_AGG , BITOR_AGG , BITXOR_AGG |
Boolean
|
BOOLAND_AGG , BOOLOR_AGG , BOOLXOR_AGG |
Hash
|
HASH_AGG |
Semi-structured Data
|
ARRAY_AGG , OBJECT_AGG |
Linear Regression
|
REGR_AVGX , REGR_AVGY , REGR_COUNT , REGR_INTERCEPT , REGR_R2 , REGR_SLOPE , REGR_SXX , REGR_SXY , REGR_SYY |
Statistics and Probability
|
KURTOSIS , SKEW |
Cardinality Estimation
|
APPROX_COUNT_DISTINCT , HLL , HLL_ACCUMULATE , HLL_COMBINE , HLL_ESTIMATE , HLL_EXPORT , HLL_IMPORT |
Similarity Estimation
|
APPROXIMATE_JACCARD_INDEX , APPROXIMATE_SIMILARITY , MINHASH , MINHASH_COMBINE |
Frequency Estimation
|
APPROX_TOP_K , APPROX_TOP_K_ACCUMULATE , APPROX_TOP_K_COMBINE , APPROX_TOP_K_ESTIMATE |
Percentile Estimation
|
APPROX_PERCENTILE , APPROX_PERCENTILE_ACCUMULATE , APPROX_PERCENTILE_COMBINE , APPROX_PERCENTILE_ESTIMATE |
Aggregation Utilities
|
GROUPING , GROUPING_ID |
Window Functions¶
Window functions are aggregate functions that can operate on a subset of rows within the set of input rows.
Table Functions¶
Category/Sub-category |
Functions |
|---|---|
Data Loading
|
VALIDATE |
Data Generation
|
GENERATOR |
Object Modeling
|
GET_OBJECT_REFERENCES |
Semi-structured Queries
|
FLATTEN |
Query Results
|
RESULT_SCAN |
Historical & Usage Information . (Information Schema) |
|
Queries
|
QUERY_HISTORY , QUERY_HISTORY_BY_* ( SESSION | USER | WAREHOUSE ) |
Warehouse & Storage Usage
|
DATABASE_STORAGE_USAGE_HISTORY , STAGE_STORAGE_USAGE_HISTORY , WAREHOUSE_METERING_HISTORY |
Data Loading & Transfer
|
COPY_HISTORY , DATA_TRANSFER_HISTORY , PIPE_USAGE_HISTORY |
User Login
|
LOGIN_HISTORY , LOGIN_HISTORY_BY_USER |
System Functions¶
Category |
Functions |
|---|---|
Control
|
SYSTEM$ABORT_SESSION , SYSTEM$ABORT_TRANSACTION , SYSTEM$CANCEL_ALL_QUERIES , SYSTEM$CANCEL_QUERY , SYSTEM$PIPE_FORCE_RESUME , SYSTEM$USER_TASK_CANCEL_ONGOING_EXECUTIONS, SYSTEM$WAIT |
Information
|
SYSTEM$CLUSTERING_DEPTH , SYSTEM$CLUSTERING_INFORMATION , SYSTEM$GENERATE_SCIM_ACCESS_TOKEN , SYSTEM$LAST_CHANGE_COMMIT_TIME , SYSTEM$PIPE_STATUS , SYSTEM$TYPEOF , SYSTEM$VERIFY_EXTERNAL_OAUTH_TOKEN, SYSTEM$WHITELIST , SYSTEM$WHITELIST_PRIVATELINK |
Query Information
|
EXPLAIN_JSON , SYSTEM$EXPLAIN_JSON_TO_TEXT, SYSTEM$EXPLAIN_PLAN_JSON |
User-defined Functions (UDFs)¶
In addition to the system-defined functions provided by Snowflake, users can create functions. Snowflake supports two types of UDFs:
Type |
Notes |
|---|---|
SQL |
SQL UDFs can be defined to return either scalar or tabular output. |
JavaScript |
JavaScript UDFs can be defined to return either scalar or tabular output. |