Summary of Operators & Functions

Snowflake supports most of the standard operators and functions defined in SQL:1999, as well as parts of the SQL:2003 analytic extensions.

In this Topic:

Operators

Category/Sub-category Operators
Arithmetic Operators + , - , * , / , %
Comparison Operators = , != , <> , < , <= , > , >=
Logical/Boolean Operators AND , NOT , OR

Scalar Functions

Category/Sub-category Functions
Bitwise Expression Functions BITAND , BITNOT , BITOR , BITSHIFTLEFT , BITSHIFTRIGHT , BITXOR (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
Session
CURRENT_* ( ROLE | SESSION | STATEMENT | TRANSACTION | USER ) , LAST_QUERY_ID , LAST_TRANSACTION
Session Object
CURRENT_* ( DATABASE | SCHEMA | SCHEMAS | WAREHOUSE )
Numeric Functions  
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 , CHARINDEX , CHR / CHR , CONCAT / || , CONTAINS , EDITDISTANCE , ENDSWITH , ILIKE , INITCAP , INSERT , LEFT , LENGTH , LIKE , LOWER , LPAD , LTRIM , OCTECT_LENGTH , PARSE_URL , POSITION , REPEAT , REPLACE , REVERSE , RIGHT , RPAD , RTRIM , RTRIMMED_LENGTH , SPACE , SPLIT , SPLIT_PART , STARTSWITH , SUBSTR / SUBSTRING , TRANSLATE , TRIM , UPPER, UUID_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 )
Crytographic Hash
MD5 / MD5_HEX , MD5_BINARY , SHA1 / SHA1_HEX , SHA1_BINARY , SHA2 / SHA2_HEX , SHA2_BINARY
Regular Expression (String) Functions REGEXP , REGEXP_COUNT , REGEXP_INSTR , REGEXP_LIKE , REGEXP_REPLACE , REGEXP_SUBSTR , RLIKE
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 , YEAROFWEEK_ISO
Addition/Subtraction
ADD_MONTHS , DATEADD, DATEDIFF , TIMEADD, TIMEDIFF , TIMESTAMPADD , TIMESTAMPDIFF
Truncation
DATE_TRUNC , TRUNC
Type Conversion
TO_DATE , TO_TIME , TO_TIMESTAMP , TO_TIMESTAMP_* ( LTZ | NTZ | TZ )
Time Zone
CONVERT_TIMEZONE
Semi-structured Data Functions  
Parsing
CHECK_JSON , CHECK_XML , 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
Conversion Functions CAST , TO_* (all supported Snowflake data types) , TRY_CAST , TRY_TO_* ( numeric, Boolean, date & time data types )
Aggregate Functions  
Utilities
GROUPING , GROUPING_ID
General
ANY_VALUE , AVG , CORR , COUNT , COVAR_POP , COVAR_SAMP , HASH_AGG , LISTAGG , MAX , MEDIAN , MIN , PERCENTILE_CONT , PERCENTILE_DISC , STDDEV / STDDEV_POP , STDDEV_SAMP , SUM , VAR_POP / VARIANCE_POP , VAR_SAMP / VARIANCE_SAMP / VARIANCE
Bitwise
BITAND_AGG , BITOR_AGG , BITXOR_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
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
Analytic / Window Functions CUME_DIST , DENSE_RANK , FIRST_VALUE , LAG , LAST_VALUE , LEAD , NTH_VALUE , NTILE , PERCENT_RANK , RANK , ROW_NUMBER , WIDTH_BUCKET
Miscellaneous Functions  
System
SYSTEM$ABORT_SESSION , SYSTEM$ABORT_TRANSACTION , SYSTEM$CANCEL_ALL_QUERIES , SYSTEM$CANCEL_QUERY , SYSTEM$CLUSTERING_DEPTH , SYSTEM$CLUSTERING_INFORMATION , SYSTEM$CLUSTERING_RATIO , SYSTEM$LAST_CHANGE_COMMIT_TIME , SYSTEM$PIPE_STATUS , SYSTEM$TYPEOF , SYSTEM$WAIT
Utility
GET_DDL , HASH
Data Generation
RANDOM , SEQ1 / SEQ2 / SEQ4 / SEQ8 , UUID_STRING
Random Distribution
NORMAL , RANDSTR , UNIFORM , ZIPF

Table Functions

Category/Sub-category Functions
Table Functions  
Data Loading
VALIDATE
Data Generation
GENERATOR
Semi-structured Queries
FLATTEN
Query Results
RESULT_SCAN
Table Functions (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
User-defined Table Functions (UDTFs) SQL or JavaScript

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 table output.
JavaScript JavaScript UDFs can be defined to return either scalar or table output.