Complete list of Scalar Functions in snowflake

The complete list of Scalar Functions in Snowflake are as follows. SCALAR Functions are based on user input. Scalar functions may take single or multiple arguments, but they always return a single-valued result which is mandatory.The Scalar function works on each record independently. SCALAR Functions are based on user input. Scalar functions may take single or multiple arguments, but they always return a single-valued result which is mandatory.

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
ALL_USER_NAMES, 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 : 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 , 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 , 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_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 , 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

Author: user

Leave a Reply