Built-in functions
Last updated
Last updated
This section describes each SQL function individually. ().
None Note: Snowflake does not contain a similar built-in function, a UDF might be created to emulate the Sybase behavior.
None Note: Snowflake does not contain a similar built-in function.
None Note: Snowflake does not contain a similar built-in function.
None Note: Snowflake does not contain a similar built-in function.
None Note: Snowflake does not contain a similar built-in function.
None Note: Snowflake does not contain a similar built-in function.
None Note: Snowflake does not contain a similar built-in function.
None Note: Snowflake does not contain a similar built-in function.
None Note: Snowflake does not contain a similar built-in function.
None Note: Snowflake does not contain a similar built-in function.
None Note: Pending Transformation.
None Note: Pending Transformation.
None Note: Pending Transformation.
None Note: Pending Transformation.
DAYNAME_UDF(date-expression )
DAYS_UDF( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )
DB_ID_UDF Note: Snowflake does not have any built-in function to emulate this behavior.
CURRENT_DATABASE( )
None Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
HOURS_UDF( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )
None Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
None
IS_DATE_UDF
IS_NUMERIC_UDF( string-expression )
([ALL | DISTINCT] string-expresssion [, 'delimiter-string'] [ORDER BY order-by-expression [ ASC | DESC ], ... ] ) [OVER ...]
([ DISTINCT ] expr1 [, delimiter ] ) [ WITHIN GROUP ( orderby_clause ) ] OVER ( [ PARTITION BY expr2 ] ) Note: ALL Keyword not supported in snowflake.
MINUTES_UDF( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )
MONTHNAME_UDF( date-expression )
MONTH_UDF( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )
None Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
OBJECT_ID_UDF
None Note: Snowflake does not have any built-in function to emulate this behavior.
PATINDEX_UDF( '%pattern%', string-expression )
None Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
Note: Snowflake is case-sensitive
SECONDS_UDF( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )
None
Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
STR_UDF
None Note: Snowflake does not have any built-in function to emulate this behavior.
CURRENT_DATE( )
None Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
None Note: Snowflake does not have any built-in function to emulate this behavior.
WEEKS_UDF( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )
None Note: Snowflake does not have any built-in function to emulate this behavior.
YEARS_UDF( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )
( numeric-expression)
( numeric-expression)
( numeric-expression)
( numeric-expression)
(integer-expression, expression [ , …] )
( string-expression)
( numeric-expression)
( numeric-expression)
( numeric-expression)
( numeric-expression)
( numeric-expression)
(numeric-expression, numeric-expression)
( numeric-expression, numeric-expression)
(Â [DISTINCT] column-name) [OVER ...]
(Â [DISTINCT] column-name) [OVER ...]
( file-name-expression, large-object-column )
(integer-expression)
(large-object-column)
(string_or_binary) Note: Snowflake doesn't use fractional bytes, so length is always calculated as 8 * OCTET_LENGTH.
( insert-position , source-string , insert-string )
(large-object-column)
(string-expression)
(expression AS data type)
(source_expr AS target_data_type)
(numeric-expression)
( input_expr [, scale_expr ] )
(integer-expression)
(integer-expression)
(string-expression)
(string or binary-expression)
(long-varchar-expression)
(string or binary-expression)
(string-expression1, string-expression2)
string-expression1, string-expression2. [start-pos])
(expression, expression, [...])
(expression, expression, [...])
(table-name, column-name)
(table-id, column-id [, database-id])
( { integer-expression1 | string-expression } … [ , integer-expression2 ] )
( data-type, expression [ , format-style ] )
(source_expr AS target_data_type)
( dependent-expression, independent-expression ) [OVER ...]
( dependent-expression, independent-expression ) [OVER ...]
( numeric-expression)
( numeric-expression)
( numeric-expression)
( numeric-expression)
( dependent-expression, independent-expression ) [OVER ...]
( dependent-expression, independent-expression ) [OVER ...]
( dependent-expression, independent-expression ) [OVER ...]
( dependent-expression, independent-expression ) [OVER ...]
( * | expression | DISTINCT column-name ) [OVER ...]
( * | expression | DISTINCT column-name ) [OVER ...]
() [OVER ...]
[OVER ...]
(string-expression)
(string-expression, [format])
( date-part, numeric-expression, date-expression )
( date-part, numeric-expression, date-expression )
( date-part, numeric-expression[, multiple-expression] )
( date-part, date-expression1, date-expression2 )
( date-part, date-expression1, date-expression2 ) Note: Transformation Needs Review.
( date-part, datetime-expression [, multiple-expression ] )
( date-part, date-expression )
( date-part, date-expression )
( date-part, datetime-expression [, multiple-expression ] )
( expression )
(expression)
( date-expression )
( date-expression )
( date-expression )
( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )
( [ database-name ] )
( [ database-id ] )
( { property-id | property-name } [ , { database-id | database-name } ] )
( numeric-expression )
( numeric-expression )
() [OVER ...]
() [OVER ...]
( string-expression1, string-expression2 )
( date-expression )
( date-expression )
( string-expression , key [ , algorithm-format [ , initialization-vector ] ] )
( value_to_encrypt , passphrase , [ [ additional_authenticated_data , ] encryption_method ] ) Note: Pending Review
( [ sqlstate | sqlcode ] )
(numeric-expression)
(numeric-expression)
(expression, period-expression) [OVER ...]
( date-part FROM timestamp-expression )
( date-part FROM timestamp-expression ) Note: Pending Review
(numeric-expression)
(numeric-expression)
()
()
( expression [ , algorithm ] )
Note: Pending Transformation
( hexadecimal-string )
( hexadecimal-string )
( datetime-expression )
( datetime-expression )
( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )
( string-expression )
( string-expression )
( string-expression )
( string-expression )
( header-field-name [ , instance ] )
( header-field-name [ , instance ] )
( var-name [ , instance [ , attribute ] ] )
( expression1, expression2 [ , expression3 ] )
Note: Is transformed to IFF(input is null, expression2, expression3) when the expression3 is present if not the third parameter will be NULL.
( numeric-expression, base_expr, insert_expr )
( base_expr, pos, len, insert_expr )
(integer-expression)
( string-expression )
( expression, expression [ …, expression ] )
( expression, expression [ …, expression ] )
( string-expression )
( value_expr [, offset [, default ] ] ) [OVER ...]
( value_expr [, offset [, default ] ] ) [OVER ...]
(expression [IGNORE NULLS | RESPECT NULLS]) OVER ...
(expression [IGNORE NULLS | RESPECT NULLS]) OVER ...
( string-expression )
( string-expression )
( value_expr [, offset [, default ] ] ) [OVER ...]
( value_expr [, offset [, default ] ] ) [OVER ...]
( string-expression, numeric-expression )
( string-expression, numeric-expression )
( string-expression )
( string-expression )
( string-expression )
( string-expression )
(numeric-expression)
(numeric-expression)
(numeric-expression)
(numeric-expression)
(numeric-expression)
( string-expression )
( string-expression )
( str, n [, pattern ] )
( str, n [, pattern ] )
( string-expression, [ trim_character_set ] )
( string-expression, [ trim_character_set ] ) Note: Snowflake is case-sensitive by default and affects operations with strings.
([DISTINCT] column-name) [OVER ...]
(column-name) [OVER ...] Note: Usage of the DISTINCT keyword does not affect the result.
( [ ALL | DISTINCT ] expression ) [OVER ...]
( expression ) [OVER ...] Note: Usage of the ALL has no effect on the function since it counts all by default. The DISTINCT keyword is not supported.
([DISTINCT] column-name) [OVER ...]
( expression ) [OVER ...] Note: Usage of the DISTINCT keyword does not affect the result.
( datetime-expression )
( datetime-expression )
( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )
( dividend, divisor )
( dividend, divisor )
( date-expression )
( date-expression )
( date-expression )
( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )
( )
( )
( { connection-id }, { database-id } )
( string-expression )
( * )
( )
( expression1 ) OVER ( ORDER BY expression2 [ ASC | DESC ] )
( constant_value ) OVER ( [ PARTITION BY expr1 ] ORDER BY expr2 [ { ASC | DESC } ] )
( expression1, expression2 )
( expression1, expression2 )
( * )
( object-name )
( object-id [ , database-id ] )
( string-expression )
( string-expression )
( '%pattern%', string-expression )
( ) OVER ( ORDER BY expression [ ASC | DESC ] )
( ) OVER ( [ PARTITION BY expr1 ] ORDER BY expr2 [ { ASC | DESC } ] [ fixedRangeFrame ] )
( expression1 ) WITHIN GROUP ( ORDER BY expression2 [ ASC | DESC ] )
( percentile ) WITHIN GROUP (ORDER BY order_by_expr) OVER ( [ PARTITION BY expr3 ] )
( expression1> ) WITHIN GROUP ( ORDER BY expression2> [ ASC | DESC ] )
( percentile ) WITHIN GROUP (ORDER BY order_by_expr ) OVER ( [ PARTITION BY expr3 ] )
( * )
( )
( numeric-expression1, numeric-expression2 )
( numeric-expression1, numeric-expression2 )
( { property-id | property-name } )
( { property-id | property-name } )
( property-id )
( property-id )
( date-expression )
( date-expression )
( date-expression,[ quarter_start_month ] )
(numeric-expression)
(numeric-expression)
( [ integer-expression ] )
( [ integer-expression ] )
( ) OVER ( [ PARTITION BY ] ORDER BY expression [ ASC | DESC ] )
( ) OVER ( [ PARTITION BY ] ORDER BY expression [ { ASC | DESC } ] [ window_frame ] )
( filename [ , start [ , length ] ] )
( dependent-expression, independent-expression ) [OVER ...]
( dependent-expression, independent-expression ) [OVER ...]
( dependent-expression, independent-expression ) [OVER ...]
( dependent-expression, independent-expression ) [OVER ...]
( dependent-expression, independent-expression ) [OVER ...]
( dependent-expression, independent-expression ) [OVER ...]
( dependent-expression, independent-expression ) [OVER ...]
( dependent-expression, independent-expression ) [OVER ...]
( dependent-expression, independent-expression ) [OVER ...]
( dependent-expression, independent-expression ) [OVER ...]
( dependent-expression, independent-expression ) [OVER ...]
( dependent-expression, independent-expression ) [OVER ...]
( dependent-expression, independent-expression ) [OVER ...]
( dependent-expression, independent-expression ) [OVER ...]
( dependent-expression, independent-expression ) [OVER ...]
( dependent-expression, independent-expression ) [OVER ...]
( dependent-expression, independent-expression ) [OVER ...]
( dependent-expression, independent-expression ) [OVER ...]
( dividend, divisor )
( dividend, divisor )
( string-expression, integer-expression )
( string-expression, integer-expression )
( original-string, search-string, replace-string )
( original-string, search-string, replace-string )
( string-expression, integer-expression )
( string-expression, integer-expression )
( expression )
( expression )
( string-expression, numeric-expression )
( string-expression, numeric-expression )
( numeric-expression, integer-expression )
( numeric-expression, integer-expression )
OVER ( [ PARTITION BY window partition ] ORDER BY window ordering )
OVER ( [ PARTITION BY window partition ] ORDER BY window ordering )
( table-name)
( str, n [, pattern ] )
( str, n [, pattern ] )
( string-expression, [ trim_character_set ] )
( string-expression, [ trim_character_set ] )
( datetime-expression )
( datetime-expression )
( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )
(numeric-expression)
(numeric-expression)
( string-expression1, string-expression2 )
(numeric-expression)
(numeric-expression)
( string-expression [, { collation-id | collation-name [ ( collation-tailoring-string ) ] } ] )
( string-expression )
( string-expression )
(numeric-expression)
(numeric-expression)
( sql-standard-string, sql-statement-string )
(numeric-expression)
(numeric-expression)
(numeric-expression)
(numeric-expression)
( [ ALL ] expression ) [OVER ...]
( expression ) [OVER ...]
( [ ALL ] expression ) [OVER ...]
( expression ) [OVER ...]
( [ ALL ] expression ) [OVER ...]
( expression ) [OVER ...]
( numeric-expression [ , length[ , decimal ] ] )
( string_expr1, string_expr2, string_expr3 )
( string-expression [ , … ] )
([...]. '')
(string-expression)
( string-expression1, start, length, string-expression2 )
( string-expression, start [ , length ] )
( string-expression, start [ , length ] )
( string-expression, start [ , length ] )
( expression | DISTINCT column-name ) [OVER ...]
( expression | DISTINCT column-name ) [OVER ...]
( [ user-name ] )
( )
( [ user-id ] )
( )
(numeric-expression)
(numeric-expression)
( [*] )
( string-expression, [ trim_character_set ] )
( string-expression, [ trim_character_set ] )
( numeric-expression, integer-expression )
( numeric-expression, integer-expression )
(string-expression)
(string-expression)
(string-expression)
(string-expression)
( [ user-name ] )
( [ user-id ] )
( uuid-expression )
( [ ALL ] expression ) [OVER ...]
( [ ALL ] expression ) [OVER ...]
( [ ALL ] expression ) [OVER ...]
( [ ALL ] expression ) [OVER ...]
( [ ALL ] expression ) [OVER ...]
( [ ALL ] expression ) [OVER ...]
( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )
(expression) OVER (window-spec);
( expression, min_value, max_value, num_buckets )
( expression, min_value, max_value, num_buckets )
( date-expression )
( date-expression )
( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )
( integer-expression1, integer-expression2, integer-expression3 )
( year, month, day )
: GLOBAL TEMPORARY TABLE functionality not supported. : Pending Functional Equivalence Review. : Datetime interval not supported by Snowflake. : DB_ID_UDF may have a different behavior in certain cases. : CURRENT_DATABASE function has different behavior in certain cases.