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

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.