Built-in functions

Built-in Functions

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

Sybase	Snowflake Equivalent
ABS ( numeric-expression)	ABS ( numeric-expression)
ACOS ( 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)	ASIN ( numeric-expression)
ATAN ( numeric-expression)	ATAN ( numeric-expression)
ATAN2 (numeric-expression, numeric-expression)	ATAN2 ( numeric-expression, numeric-expression)
AVG ( [DISTINCT] column-name) [OVER ...]	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 (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, [...])	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 ...]	CORR( dependent-expression, independent-expression ) [OVER ...]
COS ( numeric-expression)	COS ( numeric-expression)
COT ( numeric-expression)	COT ( numeric-expression)
COVAR_POP ( dependent-expression, independent-expression ) [OVER ...]	COVAR_POP ( dependent-expression, independent-expression ) [OVER ...]
COVAR_SAMP ( 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 )	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 )	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 )	DEGREES( numeric-expression )
DENSE_RANK () [OVER ...]	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(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	FIRST_VALUE
FLOOR(numeric-expression)	FLOOR(numeric-expression)
GETDATE()	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 )	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 ...	LAST_VALUE(expression [IGNORE NULLS | RESPECT NULLS]) OVER ...
LCASE ( string-expression )	LOWER ( string-expression )
LEAD ( value_expr [, offset [, default ] ] ) [OVER ...]	LEAD ( value_expr [, offset [, default ] ] ) [OVER ...]
LEFT( string-expression, numeric-expression )	LEFT( string-expression, numeric-expression )
LEN( string-expression )	LENGTH( string-expression )
LENGTH( 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)	LN(numeric-expression)
LOG(numeric-expression)	LN(numeric-expression)
LOG10(numeric-expression)	LOG(10, N)
LOWER( string-expression )	LOWER( string-expression )
LPAD( str, n [, pattern ] )	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 )	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 )	MOD( dividend, divisor )
MONTH( date-expression )	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 )	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 )	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 )	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 )	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)	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_AVGX ( dependent-expression, independent-expression ) [OVER ...]
REGR_AVGY ( dependent-expression, independent-expression ) [OVER ...]	REGR_AVGY ( dependent-expression, independent-expression ) [OVER ...]
REGR_COUNT ( dependent-expression, independent-expression ) [OVER ...]	REGR_COUNT ( dependent-expression, independent-expression ) [OVER ...]
REGR_INTERCEPT ( dependent-expression, independent-expression ) [OVER ...]	REGR_INTERCEPT ( dependent-expression, independent-expression ) [OVER ...]
REGR_R2 ( dependent-expression, independent-expression ) [OVER ...]	REGR_R2 ( dependent-expression, independent-expression ) [OVER ...]
REGR_SLOPE ( dependent-expression, independent-expression ) [OVER ...]	REGR_SLOPE ( dependent-expression, independent-expression ) [OVER ...]
REGR_SXX ( dependent-expression, independent-expression ) [OVER ...]	REGR_SXX ( dependent-expression, independent-expression ) [OVER ...]
REGR_SXY ( dependent-expression, independent-expression ) [OVER ...]	REGR_SXY ( dependent-expression, independent-expression ) [OVER ...]
REGR_SYY ( 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 )	REPLACE( original-string, search-string, replace-string )
REPLICATE( string-expression, integer-expression )	REPEAT( string-expression, integer-expression )
REVERSE ( expression )	REVERSE ( expression )
RIGHT ( string-expression, numeric-expression )	RIGHT ( string-expression, numeric-expression )
ROUND ( numeric-expression, integer-expression )	ROUND ( numeric-expression, integer-expression )
ROW_NUMBER OVER ( [ PARTITION BY window partition ] ORDER BY window ordering )	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 ] )	RPAD( str, n [, pattern ] )
RTRIM ( string-expression, [ trim_character_set ] )	RTRIM( string-expression, [ trim_character_set ] ) Note: Snowflake is case-sensitive
SECOND( datetime-expression )	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)	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)	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 )	SOUNDEX( string-expression )
SPACE(numeric-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)	SQRT (numeric-expression)
SQUARE (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 ...]	SUM ( expression | DISTINCT column-name ) [OVER ...]
SUSER_ID ( [ user-name ] )	CURRENT_USER( )
SUSER_NAME ( [ user-id ] )	CURRENT_USER( )
TAN (numeric-expression)	TAN (numeric-expression)
TODAY( [*] )	CURRENT_DATE( )
TRIM( string-expression, [ trim_character_set ] )	TRIM( string-expression, [ trim_character_set ] )
TRUNCNUM( numeric-expression, integer-expression )	TRUNC( numeric-expression, integer-expression )
UCASE(string-expression)	UPPER(string-expression)
UPPER(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_POP( [ ALL ] expression ) [OVER ...]
VAR_SAMP( [ ALL ] expression ) [OVER ...]	VAR_SAMP( [ ALL ] expression ) [OVER ...]
VARIANCE ( [ 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 )	WIDTH_BUCKET ( expression, min_value, max_value, num_buckets )
YEAR( date-expression )	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 )

Related EWIs

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.