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 , CURRENT_DATE , CURRENT_IP_ADDRESS, CURRENT_TIME , CURRENT_TIMESTAMP , CURRENT_VERSION , LOCALTIME , LOCALTIMESTAMP , SYSDATE
Session

ALL_USER_NAMES , CURRENT_ACCOUNT , CURRENT_ROLE , CURRENT_SESSION , CURRENT_STATEMENT , CURRENT_TRANSACTION , CURRENT_USER , LAST_QUERY_ID , LAST_TRANSACTION
Session Object

CURRENT_DATABASE , CURRENT_SCHEMA , CURRENT_SCHEMAS , CURRENT_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 , CHAR / CHR , CONCAT / || , CONCAT_WS , INSERT , LENGTH, LEN , LPAD , LTRIM , OCTET_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 , SUBSTRING
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_LOWER64 , MD5_NUMBER_UPPER64 , 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 , MONTHS_BETWEEN , TIMEADD , TIMEDIFF , TIMESTAMPADD , TIMESTAMPDIFF
Truncation

DATE_TRUNC , TIME_SLICE , TRUNCATE , TRUNC
Type Conversion

TO_DATE , DATE , TO_TIME , TIME , TO_TIMESTAMP / 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_CONSTRUCT_KEEP_NULL , 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_GEOGPOINTFROMGEOHASH , 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 — Deprecated , 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 / MAX , 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 & Row Access Policies

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

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.