SnowConvert AI - Sybase IQ - Built-in functions¶
Note
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) | 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 ) |
([ALL | DISTINCT] string-expresssion | ([ DISTINCT ] expr1 |
| 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 ) |