Built-in functions

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

For more information about built-in functions and their Snowflake equivalents, also see Common built-in functions.

Built-in Functions

This section describes each SQL function individually. (Sybase SQL Language Reference Functions).

Sybase

Snowflake Equivalent

ABS ( numeric-expression)

ACOS ( numeric-expression)

ARGN (integer-expression, expression [ , …] )

None Note: Snowflake does not contain a similar built-in function, a UDF might be created to emulate the Sybase behavior.

ASCII ( string-expression)

ASCII ( numeric-expression)

ASIN ( numeric-expression)

ATAN ( numeric-expression)

ATAN2 (numeric-expression, numeric-expression)

ATAN2 ( numeric-expression, numeric-expression)

AVG ( [DISTINCT] column-name) [OVER ...]

BFILE ( file-name-expression, large-object-column )

None Note: Snowflake does not contain a similar built-in function.

BIGINTTOHEX (integer-expression)

None Note: Snowflake does not contain a similar built-in function.

BIT_LENGTH (large-object-column)

BIT_LENGTH (string_or_binary) Note: Snowflake doesn't use fractional bytes, so length is always calculated as 8 * OCTET_LENGTH.

BYTE_INSERTSTR( insert-position , source-string , insert-string )

None Note: Snowflake does not contain a similar built-in function.

BYTE_LENGTH64(large-object-column)

None Note: Snowflake does not contain a similar built-in function.

BYTE_LENGTH(string-expression)

None Note: Snowflake does not contain a similar built-in function.

CAST (expression AS data type)

CAST(source_expr AS target_data_type)

CEIL (numeric-expression)

CEIL( input_expr [, scale_expr ] )

CHAR (integer-expression)

CHAR_LENGTH (string-expression)

LENGTH (string or binary-expression)

CHAR_LENGTH64(long-varchar-expression)

LENGTH (string or binary-expression)

CHARINDEX(string-expression1, string-expression2)

CHARINDEXstring-expression1, string-expression2. [start-pos])

COALESCE (expression, expression, [...])

COL_LENGTH (table-name, column-name)

None Note: Snowflake does not contain a similar built-in function.

COL_NAME(table-id, column-id [, database-id])

None Note: Snowflake does not contain a similar built-in function.

CONNECTION_PROPERTY( { integer-expression1 | string-expression } … [ , integer-expression2 ] )

None Note: Snowflake does not contain a similar built-in function.

CONVERT( data-type, expression [ , format-style ] )

CAST(source_expr AS target_data_type)

CORR( dependent-expression, independent-expression ) [OVER ...]

COS ( numeric-expression)

COT ( numeric-expression)

COVAR_POP ( dependent-expression, independent-expression ) [OVER ...]

COVAR_SAMP ( dependent-expression, independent-expression ) [OVER ...]

COUNT( * | expression | DISTINCT column-name ) [OVER ...]

COUNT ( * | expression | DISTINCT column-name ) [OVER ...]

CUME_DIST () [OVER ...]

CUME_DIST [OVER ...]

DATE(string-expression)

DATE(string-expression, [format])

DATEADD( date-part, numeric-expression, date-expression )

DATECEILING ( date-part, numeric-expression[, multiple-expression] )

None Note: Snowflake does not contain a similar built-in function.

DATEDIFF( date-part, date-expression1, date-expression2 )

DATEDIFF ( date-part, date-expression1, date-expression2 ) Note: Transformation Needs Review.

DATEFLOOR ( date-part, datetime-expression [, multiple-expression ] )

None Note: Pending Transformation.

DATENAME ( date-part, date-expression )

None Note: Pending Transformation.

DATEPART( date-part, date-expression )

None Note: Pending Transformation.

DATEROUND( date-part, datetime-expression [, multiple-expression ] )

None Note: Pending Transformation.

DATETIME( expression )

TO_TIMESTAMP (expression)

DAY( date-expression )

DAYNAME( date-expression )

DAYNAME_UDF(date-expression )

DAYS( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )

DAYS_UDF( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )

DB_ID( [ database-name ] )

DB_ID_UDF Note: Snowflake does not have any built-in function to emulate this behavior.

DB_NAME( [ database-id ] )

CURRENT_DATABASE( )

DB_PROPERTY( { property-id | property-name } [ , { database-id | database-name } ] )

None Note: Snowflake does not have any built-in function to emulate this behavior.

DEGREES( numeric-expression )

DENSE_RANK () [OVER ...]

DIFFERENCE( string-expression1, string-expression2 )

None Note: Snowflake does not have any built-in function to emulate this behavior.

DOW( date-expression )

DAYOFWEEK( date-expression )

ENCRYPT( string-expression , key [ , algorithm-format [ , initialization-vector ] ] )

ENCRYPT( value_to_encrypt , passphrase , [ [ additional_authenticated_data , ] encryption_method ] ) Note: Pending Review

ERRORMSG( [ sqlstate | sqlcode ] )

None Note: Snowflake does not have any built-in function to emulate this behavior.

EXP(numeric-expression)

EXP_WEIGHTED_AVG(expression, period-expression) [OVER ...]

None Note: Snowflake does not have any built-in function to emulate this behavior.

EXTRACT( date-part FROM timestamp-expression )

EXTRACT( date-part FROM timestamp-expression ) Note: Pending Review

FIRST_VALUE

FLOOR(numeric-expression)

GETDATE()

HASH( expression [ , algorithm ] )

HASH Note: Pending Transformation

HEXTOBIGINT( hexadecimal-string )

None Note: Snowflake does not have any built-in function to emulate this behavior.

HEXTOINT( hexadecimal-string )

None Note: Snowflake does not have any built-in function to emulate this behavior.

HOUR( datetime-expression )

HOURS( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )

HOURS_UDF( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )

HTML_DECODE( string-expression )

None Note: Snowflake does not have any built-in function to emulate this behavior.

HTML_ENCODE( string-expression )

None Note: Snowflake does not have any built-in function to emulate this behavior.

HTTP_DECODE( string-expression )

None Note: Snowflake does not have any built-in function to emulate this behavior.

HTTP_ENCODE( string-expression )

None Note: Snowflake does not have any built-in function to emulate this behavior.

HTTP_HEADER( header-field-name [ , instance ] )

None Note: Snowflake does not have any built-in function to emulate this behavior.

HTTP_RESPONSE_HEADER( header-field-name [ , instance ] )

None Note: Snowflake does not have any built-in function to emulate this behavior.

HTTP_VARIABLE( var-name [ , instance [ , attribute ] ] )

None Note: Snowflake does not have any built-in function to emulate this behavior.

IFNULL ( expression1, expression2 [ , expression3 ] )

IFNULL Note: Is transformed to IFF(input is null, expression2, expression3) when the expression3 is present if not the third parameter will be NULL.

INSERTSTR( numeric-expression, base_expr, insert_expr )

INSERT( base_expr, pos, len, insert_expr )

INTTOHEX(integer-expression)

None

ISDATE( string-expression )

IS_DATE_UDF

ISNULL( expression, expression [ …, expression ] )

COALESCE( expression, expression [ …, expression ] )

ISNUMERIC( string-expression )

IS_NUMERIC_UDF( string-expression )

LAG( value_expr [, offset [, default ] ] ) [OVER ...]

LAG ( value_expr [, offset [, default ] ] ) [OVER ...]

LAST_VALUE(expression [IGNORE NULLS | RESPECT NULLS]) OVER ...

LCASE ( string-expression )

LOWER ( string-expression )

LEAD ( value_expr [, offset [, default ] ] ) [OVER ...]

LEFT( string-expression, numeric-expression )

LEN( string-expression )

LENGTH( string-expression )

LIST

([ALL | DISTINCT] string-expresssion [, 'delimiter-string'] [ORDER BY order-by-expression [ ASC | DESC ], ... ] ) [OVER ...]

LISTAGG

([ DISTINCT ] expr1 [, delimiter ] ) [ WITHIN GROUP ( orderby_clause ) ] OVER ( [ PARTITION BY expr2 ] ) Note: ALL Keyword not supported in snowflake.

LN(numeric-expression)

LOG(numeric-expression)

LN(numeric-expression)

LOG10(numeric-expression)

LOG(10, N)

LOWER( string-expression )

LPAD( str, n [, pattern ] )

LTRIM( string-expression, [ trim_character_set ] )

LTRIM( string-expression, [ trim_character_set ] ) Note: Snowflake is case-sensitive by default and affects operations with strings.

MAX ([DISTINCT] column-name) [OVER ...]

MAX (column-name) [OVER ...] Note: Usage of the DISTINCT keyword does not affect the result.

MEDIAN ( [ ALL | DISTINCT ] expression ) [OVER ...]

MEDIAN ( 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.

MIN ([DISTINCT] column-name) [OVER ...]

MIN ( expression ) [OVER ...] Note: Usage of the DISTINCT keyword does not affect the result.

MINUTE( datetime-expression )

MINUTES( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )

MINUTES_UDF( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )

MOD( dividend, divisor )

MONTH( date-expression )

MONTHNAME( date-expression )

MONTHNAME_UDF( date-expression )

MONTHS( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )

MONTH_UDF( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )

NEWID ( )

UUID_STRING( )

NEXT_CONNECTION( { connection-id }, { database-id } )

None Note: Snowflake does not have any built-in function to emulate this behavior.

NEXT_HTTP_HEADER( string-expression )

None Note: Snowflake does not have any built-in function to emulate this behavior.

NOW( * )

CURRENT_TIMESTAMP( )

NTILE ( expression1 ) OVER ( ORDER BY expression2 [ ASC | DESC ] )

NTILE ( constant_value ) OVER ( [ PARTITION BY expr1 ] ORDER BY expr2 [ { ASC | DESC } ] )

NULLIF( expression1, expression2 )

NUMBER( * )

None Note: Snowflake does not have any built-in function to emulate this behavior.

OBJECT_ID ( object-name )

OBJECT_ID_UDF

OBJECT_NAME ( object-id [ , database-id ] )

None Note: Snowflake does not have any built-in function to emulate this behavior.

OCTET_LENGTH( string-expression )

PATINDEX( '%pattern%', string-expression )

PATINDEX_UDF( '%pattern%', string-expression )

PERCENT_RANK ( ) OVER ( ORDER BY expression [ ASC | DESC ] )

PERCENT_RANK ( ) OVER ( [ PARTITION BY expr1 ] ORDER BY expr2 [ { ASC | DESC } ] [ fixedRangeFrame ] )

PERCENTILE_CONT ( expression1 ) WITHIN GROUP ( ORDER BY expression2 [ ASC | DESC ] )

PERCENTILE_CONT ( percentile ) WITHIN GROUP (ORDER BY order_by_expr) OVER ( [ PARTITION BY expr3 ] )

PERCENTILE_DISC ( expression1> ) WITHIN GROUP ( ORDER BY expression2> [ ASC | DESC ] )

PERCENTILE_DISC ( percentile ) WITHIN GROUP (ORDER BY order_by_expr ) OVER ( [ PARTITION BY expr3 ] )

PI ( * )

PI ( )

POWER ( numeric-expression1, numeric-expression2 )

PROPERTY ( { property-id | property-name } )

None Note: Snowflake does not have any built-in function to emulate this behavior.

PROPERTY_DESCRIPTION ( { property-id | property-name } )

None Note: Snowflake does not have any built-in function to emulate this behavior.

PROPERTY_IS_TRACKABLE ( property-id )

None Note: Snowflake does not have any built-in function to emulate this behavior.

PROPERTY_NAME ( property-id )

None Note: Snowflake does not have any built-in function to emulate this behavior.

PROPERTY_NUMBER

None Note: Snowflake does not have any built-in function to emulate this behavior.

QUARTER ( date-expression )

QUARTERSTR ( date-expression,[ quarter_start_month ] )

None Note: Snowflake does not have any built-in function to emulate this behavior.

RADIANS (numeric-expression)

RAND ( [ integer-expression ] )

RANDOM ( [ integer-expression ] )

RANK ( ) OVER ( [ PARTITION BY ] ORDER BY expression [ ASC | DESC ] )

RANK ( ) OVER ( [ PARTITION BY ] ORDER BY expression [ { ASC | DESC } ] [ window_frame ] )

READ_SERVER_FILE ( filename [ , start [ , length ] ] )

None Note: Snowflake does not have any built-in function to emulate this behavior.

REGR_AVGX ( dependent-expression, independent-expression ) [OVER ...]

REGR_AVGY ( dependent-expression, independent-expression ) [OVER ...]

REGR_COUNT ( dependent-expression, independent-expression ) [OVER ...]

REGR_INTERCEPT ( dependent-expression, independent-expression ) [OVER ...]

REGR_R2 ( dependent-expression, independent-expression ) [OVER ...]

REGR_SLOPE ( dependent-expression, independent-expression ) [OVER ...]

REGR_SXX ( dependent-expression, independent-expression ) [OVER ...]

REGR_SXY ( dependent-expression, independent-expression ) [OVER ...]

REGR_SYY ( dependent-expression, independent-expression ) [OVER ...]

REMAINDER ( dividend, divisor )

MOD( dividend, divisor )

REPEAT ( string-expression, integer-expression )

REPEAT( string-expression, integer-expression )

REPLACE( original-string, search-string, replace-string )

REPLICATE( string-expression, integer-expression )

REPEAT( string-expression, integer-expression )

REVERSE ( expression )

RIGHT ( string-expression, numeric-expression )

ROUND ( numeric-expression, integer-expression )

ROW_NUMBER OVER ( [ PARTITION BY window partition ] ORDER BY window ordering )

ROWID( table-name)

None Note: Snowflake does not have any built-in function to emulate this behavior.

RPAD( str, n [, pattern ] )

RTRIM ( string-expression, [ trim_character_set ] )

RTRIM( string-expression, [ trim_character_set ] )

Note: Snowflake is case-sensitive

SECOND( datetime-expression )

SECONDS( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )

SECONDS_UDF( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )

SIGN (numeric-expression)

SIMILAR ( string-expression1, string-expression2 )

None

Note: Snowflake does not have any built-in function to emulate this behavior.

SIN (numeric-expression)

SORTKEY ( string-expression [, { collation-id | collation-name [ ( collation-tailoring-string ) ] } ] )

None Note: Snowflake does not have any built-in function to emulate this behavior.

SOUNDEX( string-expression )

SPACE(numeric-expression)

SQLFLAGGER( sql-standard-string, sql-statement-string )

None Note: Snowflake does not have any built-in function to emulate this behavior.

SQRT (numeric-expression)

SQUARE (numeric-expression)

STDDEV ( [ ALL ] expression ) [OVER ...]

STDDEV ( expression ) [OVER ...]

STDDEV_POP ( [ ALL ] expression ) [OVER ...]

STDDEV_POP ( expression ) [OVER ...]

STDDEV_SAMP ( [ ALL ] expression ) [OVER ...]

STDDEV_SAMP ( expression ) [OVER ...]

STR( numeric-expression [ , length[ , decimal ] ] )

STR_UDF

STR_REPLACE( string_expr1, string_expr2, string_expr3 )

REPLACE

STRING( string-expression [ , … ] )

ARRAY_TO_STRING([...]. '')

STRTOUUID (string-expression)

None Note: Snowflake does not have any built-in function to emulate this behavior.

STUFF ( string-expression1, start, length, string-expression2 )

INSERT

SUBSTRING( string-expression, start [ , length ] )

SUBSTR

SUBSTR( string-expression, start [ , length ] )

SUBSTR

SUBSTRING64( string-expression, start [ , length ] )

SUBSTR

SUM ( expression | DISTINCT column-name ) [OVER ...]

SUSER_ID ( [ user-name ] )

CURRENT_USER( )

SUSER_NAME ( [ user-id ] )

CURRENT_USER( )

TAN (numeric-expression)

TODAY( [*] )

CURRENT_DATE( )

TRIM( string-expression, [ trim_character_set ] )

TRUNCNUM( numeric-expression, integer-expression )

TRUNC( numeric-expression, integer-expression )

UCASE(string-expression)

UPPER(string-expression)

USER_ID( [ user-name ] )

None Note: Snowflake does not have any built-in function to emulate this behavior.

USER_NAME( [ user-id ] )

None Note: Snowflake does not have any built-in function to emulate this behavior.

UUIDTOSTR( uuid-expression )

None Note: Snowflake does not have any built-in function to emulate this behavior.

VAR_POP( [ ALL ] expression ) [OVER ...]

VAR_SAMP( [ ALL ] expression ) [OVER ...]

VARIANCE ( [ ALL ] expression ) [OVER ...]

WEEKS( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )

WEEKS_UDF( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )

WEIGHTED_AVG (expression) OVER (window-spec);

None Note: Snowflake does not have any built-in function to emulate this behavior.

WIDTH_BUCKET ( expression, min_value, max_value, num_buckets )

YEAR( date-expression )

YEARS( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )

YEARS_UDF( datetime-expression ) | ( datetime-expression, datetime-expression ) | ( datetime-expression, integer-expression )

YMD( integer-expression1, integer-expression2, integer-expression3 )

DATE_FROM_PARTS ( year, month, day )

SSC-FDM-0009: GLOBAL TEMPORARY TABLE functionality not supported. SSC-EWI-0073: Pending Functional Equivalence Review. SSC-EWI-TS0060: Datetime interval not supported by Snowflake. SSC-FDM-TS0025: DB_ID_UDF may have a different behavior in certain cases. SSC-FDM-TS0010: CURRENT_DATABASE function has different behavior in certain cases.\

PreviousSELECT NextData Types

Last updated 5 months ago

hashtagBuilt-in Functions

hashtagRelated EWIs

Built-in Functions

Related EWIs