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
Aggregate Functions
Window Functions
Table Functions
System Functions
User-defined Functions (UDFs)
External Functions

Scalar Functions¶

Category/Sub-category	Functions
Bitwise Expression Functions
	BITAND , BITNOT , BITOR , BITSHIFTLEFT , BITSHIFTRIGHT , BITXOR , GETBIT (see also: Bitwise Aggregation Functions)
Conditional Expression 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
Context Functions
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 ), INVOKER_ROLE , INVOKER_SHARE , IS_GRANTED_TO_INVOKER_ROLE, IS_ROLE_IN_SESSION
Data Generation Functions
	RANDOM , SEQ1 / SEQ2 / SEQ4 / SEQ8 , UUID_STRING
Numeric Functions
Arithmetic	DIV0
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
String & Binary Functions
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
Encryption/Decryption	DECRYPT , DECRYPT_RAW , ENCRYPT , ENCRYPT_RAW
Hash (non-cryptographic)	HASH , HASH_AGG
Date & Time Functions
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
Semi-structured Data Functions
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_INTERSECTION , ARRAY_POSITION , ARRAY_PREPEND , ARRAY_SIZE , ARRAY_SLICE , ARRAY_TO_STRING , OBJECT_AGG , OBJECT_CONSTRUCT , OBJECT_DELETE , OBJECT_INSERT , OBJECT_PICK
Data Extraction	FLATTEN , GET , GET_PATH , OBJECT_KEYS, XMLGET
Casts	AS_* (all data types) , TO_ARRAY , TO_JSON , TO_OBJECT , TO_VARIANT, TO_XML
Type Predicates	IS_* (all data types) , TYPEOF
Geospatial Functions
Parsing	TO_GEOGRAPHY , TRY_TO_GEOGRAPHY , ST_GEOGFROMGEOHASH, ST_GEOGRAPHYFROMWKB , ST_GEOGRAPHYFROMWKT
Formatting	ST_ASEWKB , ST_ASEWKT , ST_ASGEOJSON , ST_ASWKB / ST_ASBINARY , ST_ASWKT / ST_ASTEXT , ST_GEOHASH
Construction	ST_MAKELINE , ST_MAKEPOINT / ST_POINT , ST_MAKEPOLYGON / ST_POLYGON
Accessor	ST_DIMENSION , ST_SRID , ST_X , ST_XMAX , ST_XMIN , ST_Y , ST_YMAX , ST_YMIN
Relationship/Measurement	ST_AREA , ST_AZIMUTH , ST_CONTAINS , ST_COVEREDBY , ST_COVERS , ST_DISJOINT, ST_DISTANCE , ST_DWITHIN , ST_HAUSDORFFDISTANCE , ST_INTERSECTS , ST_LENGTH , ST_NPOINTS / ST_NUMPOINTS , ST_PERIMETER , ST_WITHIN
Transformation	ST_CENTROID , ST_COLLECT , ST_ENVELOPE , ST_SIMPLIFY
Conversion Functions
	CAST , TO_* (all supported Snowflake data types) , TRY_CAST , TRY_TO_* ( numeric, Boolean, date & time data types )
Utility & Hash Functions
Utility	GET_DDL , HASH

Aggregate Functions¶

Category/Sub-category	Functions
Aggregate Functions
General	ANY_VALUE , AVG , CORR , COUNT , COUNT_IF, 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
General
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 ) , EXTERNAL_FUNCTIONS_HISTORY
Warehouse & Storage Usage	DATABASE_STORAGE_USAGE_HISTORY , STAGE_STORAGE_USAGE_HISTORY , WAREHOUSE_METERING_HISTORY
Column-level Security	POLICY_REFERENCES
Data Loading & Transfer	COPY_HISTORY , DATA_TRANSFER_HISTORY , PIPE_USAGE_HISTORY
SCIM	REST_EVENT_HISTORY
User Login	LOGIN_HISTORY , LOGIN_HISTORY_BY_USER
Search Optimization Maintenance	SEARCH_OPTIMIZATION_HISTORY

System Functions¶

Category	Functions
System Functions
Control	SYSTEM$ABORT_SESSION , SYSTEM$ABORT_TRANSACTION , SYSTEM$CANCEL_ALL_QUERIES , SYSTEM$CANCEL_QUERY , SYSTEM$DISABLE_BEHAVIOR_CHANGE_BUNDLE , SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE , SYSTEM$PIPE_FORCE_RESUME , SYSTEM$USER_TASK_CANCEL_ONGOING_EXECUTIONS, SYSTEM$WAIT
Information	SYSTEM$BEHAVIOR_CHANGE_BUNDLE_STATUS , SYSTEM$CLUSTERING_DEPTH , SYSTEM$CLUSTERING_INFORMATION , SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS, SYSTEM$GENERATE_SAML_CSR, SYSTEM$GENERATE_SCIM_ACCESS_TOKEN , SYSTEM$LAST_CHANGE_COMMIT_TIME , SYSTEM$MIGRATE_SAML_IDP_REGISTRATION , 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 the following 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.

External Functions¶

Snowflake also supports External Functions, which are stored and executed outside Snowflake.