Snowflake Data Cloud Connector > Part III: SQL ELT with Snowflake Data Cloud Connector > Mappings in SQL ELT mode for Snowflake Data Cloud > Functions in mappings in SQL ELT mode
  

Functions in mappings in SQL ELT mode

When you create expressions within a mapping in SQL ELT mode, you must use the native functions and expression syntax of Snowflake Data Cloud and not Informatica functions and expression syntax.
You can use the following native functions in a mapping in SQL ELT mode:

Aggregate functions

Function
Function
Function
ANY_VALUE( expr )
COVAR_SAMP( y, x )
REGR_INTERCEPT( y,x )
APPROX_COUNT_DISTINCT( expr )
HASH_AGG( expr )
REGR_R2( y,x )
APPROX_PERCENTILE_ACCUMULATE( expr )
HLL( expr )
REGR_SLOPE( y,x )
ARRAY_UNIQUE_AGG( column )
HLL_ACCUMULATE( expr )
REGR_SXX( y,x )
AVG( expr )
HLL_COMBINE( state )
REGR_SXY( y,x )
BITAND_AGG( expr )
KURTOSIS( expr )
REGR_SYY( y,x )
BITOR_AGG( expr )
MAX( expr )
STDDEV( expr )
BITXOR_AGG( expr )
MAX_BY( col_to_return, col_containing_max )
STDDEV_POP( expr )
BOOLAND_AGG( expr )
MEDIAN( expr )
STDDEV_SAMP( expr )
BOOLOR_AGG( expr )
MIN( expr )
SUM( expr )
BOOLXOR_AGG( expr )
MIN_BY( col_to_return, col_containing_max )
VAR_POP( expr )
CORR( y, x )
MODE( expr )
VAR_SAMP( expr )
COUNT( expr )
REGR_AVGX( y,x )
VARIANCE( expr1 )
COUNT_IF( cond )
REGR_AVGY( y,x )
VARIANCE_POP( expr )
COVAR_POP( y, x )
REGR_COUNT( y,x )
VARIANCE_SAMP( expr1 )

Bitwise expression functions

Function
Function
Function
BITAND(expr1, expr2)
BITSHIFTLEFT(expr1, n)
GETBIT(integer_expr, bit_position)
BITNOT(expr)
BITSHIFTRIGHT(expr1, n)
-
BITOR(expr1, expr2)
BITXOR(expr1, expr2)
-

Conditional expression functions

Function
Function
Function
BOOLAND( expr1, expr2 )
GREATEST( expr )
NVL( expr1,expr2 )
BOOLNOT( expr )
IFF( condition, expr1, expr2 )
NVL2( expr1,expr2,expr3 )
BOOLOR( expr1, expr2 )
IFNULL( expr1, expr2 )
REGR_VALX( expr1,expr2 )
BOOLXOR( expr1, expr2 )
LEAST( expr )
REGR_VALY( expr1,expr2 )
COALESCE( expr1,expr2 )
NULLIF( expr1,expr2 )
ZEROIFNULL( expr )
EQUAL_NULL( expr1,expr2 )
NULLIFZERO( expr )
-

Context functions

Function
Function
Function
ALL_USER_NAMES()
CURRENT_SCHEMA()
INVOKER_ROLE()
CURRENT_ACCOUNT()
CURRENT_SCHEMAS()
INVOKER_SHARE()
CURRENT_AVAILABLE_ROLES()
CURRENT_SECONDARY_ROLES()
IS_GRANTED_TO_INVOKER_ROLE( role_name )
CURRENT_CLIENT()
CURRENT_SESSION()
IS_ROLE_IN_SESSION( role_name )
CURRENT_DATABASE()
CURRENT_STATEMENT()
LAST_QUERY_ID( num )
CURRENT_DATE()
CURRENT_TIMESTAMP( fract_sec_precision )
LAST_TRANSACTION()
CURRENT_IP_ADDRESS()
CURRENT_TRANSACTION()
LOCALTIMESTAMP()
CURRENT_REGION()
CURRENT_USER()
SYSDATE()
CURRENT_ROLE()
CURRENT_VERSION()
-
CURRENT_ROLE_TYPE()
CURRENT_WAREHOUSE()
-

Conversion functions

Function
Function
Function
DATE(String)
TO_NUMBER(Boolean)
TO_VARCHAR(Binary)
DATE(Temporal)
TO_NUMBER(String)
TO_VARCHAR(Numeric)
TO_BINARY
TO_NUMERIC
TO_VARCHAR(Temporal)
TO_BINARY(String)
TO_NUMERIC(Boolean)
TRY_TO_BINARY
TO_BOOLEAN
TO_NUMERIC(String)
TRY_TO_BOOLEAN
TO_BOOLEAN(Number)
TO_TIMESTAMP
TRY_TO_DATE
TO_CHAR
TO_TIMESTAMP(String)
TRY_TO_DECIMAL
TO_CHAR(Binary)
TO_TIMESTAMP(Temporal)
TRY_TO_DOUBLE
TO_CHAR(Numeric)
TO_TIMESTAMP_LTZ
TRY_TO_NUMBER
TO_CHAR(Temporal)
TO_TIMESTAMP_LTZ(String)
TRY_TO_NUMERIC
TO_DECIMAL(Boolean)
TO_TIMESTAMP_LTZ(Temporal)
TRY_TO_TIMESTAMP
TO_DECIMAL(String)
TO_TIMESTAMP_NTZ
TRY_TO_TIMESTAMP_LTZ
TO_DOUBLE
TO_TIMESTAMP_NTZ(String)
TRY_TO_TIMESTAMP_NTZ
TO_DOUBLE(String)
TO_TIMESTAMP_NTZ(Temporal)
-
TO_NUMBER
TO_VARCHAR
-

Cortex functions

Function
SNOWFLAKE_CORTEX_COMPLETE( model, prompt_or_history )
SNOWFLAKE_CORTEX_EXTRACT_ANSWER( source_document, question )
SNOWFLAKE_CORTEX_SENTIMENT( text )
SNOWFLAKE_CORTEX_SUMMARIZE( text )
SNOWFLAKE_CORTEX_TRANSLATE( text, source_language, target_language )

Data generation functions

Function
Function
Function
NORMAL( mean,stddev,gen )
SEQ2( [0|1] )
UUID_STRING(uuid,name)
RANDOM()
SEQ4( [0|1] )
ZIPF( s, N, gen )
RANDSTR( length,gen )
SEQ8( [0|1] )
-
SEQ1( [0|1] )
UNIFORM( min,max,gen )
-

Date and time functions

Function
Function
ADD_MONTHS( date_or_time_expr, num_months_expr )
NEXT_DAY( date_or_time_expr, dow_string )
DATEADD( date_or_time_part, value, date_or_time_expr )
PREVIOUS_DAY( date_or_time_expr, dow_string )
DATEDIFF( date_or_time_part, date_or_time_expr1, date_or_time_expr2 )
QUARTER( date_or_timestamp_expr )
DATE_FROM_PARTS( year, month, day ) or DATEFROMPARTS( year, month, day )
SECOND( date_or_timestamp_expr )
DATE_PART( date_or_time_part,date_or_time_expr )
SYSDATE()
DATE_TRUNC( date_or_time_part,date_or_time_expr )
TIMESTAMPADD( date_or_time_part,value,date_or_time_expr )
DAY( date_or_timestamp_expr )
TIMESTAMPDIFF( date_or_time_part,date_or_time_expr1,date_or_time_expr2 )
DAYNAME( date_or_timestamp_expr )
TIMESTAMP_LTZ_FROM_PARTS( year, month, day, hour, minute, second ) or TIMESTAMPLTZFROMPARTS( year, month, day,hour, minute, second )
DAYOFMONTH( date_or_timestamp_expr )
TIMESTAMP_NTZ_FROM_PARTS( year, month, day, hour, minute, second ) or TIMESTAMPNTZFROMPARTS( year, month, day, hour, minute, second )
DAYOFWEEK( date_or_timestamp_expr )
TIME_SLICE( date_or_time_expr, slice_length,date_or_time_part )
DAYOFWEEKISO( date_or_timestamp_expr )
TRUNC( date_or_time_expr, date_or_time_part )
DAYOFYEAR( date_or_timestamp_expr )
WEEK( date_or_timestamp_expr )
HOUR( date_or_timestamp_expr )
WEEKISO( date_or_timestamp_expr )
LAST_DAY( date_or_time_expr )
WEEKOFYEAR( date_or_timestamp_expr )
MINUTE( date_or_timestamp_expr )
YEAR( date_or_timestamp_expr )
MONTH( date_or_timestamp_expr )
YEAROFWEEK( date_or_timestamp_expr )
MONTHNAME( date_or_time_expr )
YEAROFWEEKISO( date_or_timestamp_expr )
MONTHS_BETWEEN( date_expr1, date_expr2 )
-

Encryption functions

Function
DECRYPT( value_to_decrypt,passphrase )
DECRYPT_RAW( value_to_encrypt,key,iv )
ENCRYPT( value_to_encrypt,passphrase )
ENCRYPT_RAW( value_to_encrypt,key,iv )

Numeric functions

Function
Function
Function
ABS( expr )
DEGREES( real_expr )
ROUND( input_expr )
ACOS( real_expr )
DIV0( dividend,divisor )
SIGN( expr )
ACOSH( real_expr )
DIV0NULL( dividend,divisor )
SIN( real_expr )
ASIN( real_expr )
EXP( real_expr )
SINH( real_expr )
ASINH( real_expr )
FACTORIAL( integer_expr )
SQRT( expr )
ATAN( real_expr )
FLOOR( expr )
SQUARE( expr )
ATAN2( y,x )
LN( expr )
TAN( real_expr )
ATANH( real_expr )
LOG( base,expr )
TANH( real_expr )
CBRT( expr )
MOD( expr1,expr2 )
TRUNCATE( expr )
CEIL( expr )
PI( real_expr )
TRUNC( expr )
COS( real_expr )
POW( x,y )
WIDTH_BUCKET( expr,min_value,max_value,num_buckets )
COSH( real_expr )
POWER( x,y )
-
COT( real_expr )
RADIANS( real_expr )
-

Regular expression functions

Function
REGEXP_COUNT(subject,pattern)
REGEXP_INSTR(subject,pattern)
REGEXP_LIKE(subject,pattern)
REGEXP_REPLACE(subject,pattern)
REGEXP_SUBSTR(subject,pattern)
REGEXP_SUBSTR_ALL(subject,pattern)
RLIKE(subject,pattern)

String and binary functions

Function
Function
Function
ASCII( expr )
JAROWINKLER_SIMILARITY( string_expr1,string_expr2 )
RTRIMMED_LENGTH( expr )
BASE64_DECODE_BINARY(input [, alphabet ])
LEFT( string_expr,length_expr )
SHA1( expr )
BASE64_DECODE_STRING(input [, alphabet ])
LEN( expr )
SHA1_HEX( expr )
BASE64_ENCODE( input )
LENGTH( expr )
SHA1_BINARY( expr )
BIT_LENGTH( expr )
LIKE( subject, pattern )
SHA2_HEX( expr )
BIT_LENGTH( expr )
LOWER( expr )
SHA2( expr )
CHR( expr )
LPAD( expr1, expr2 )
SHA2_BINARY( expr )
CHAR( expr )
LPAD( expr1, expr2, expr3 )
SOUNDEX( varchar_expr )
CHARINDEX( expr1, expr2 )
LTRIM( expr )
SOUNDEX_P123( varchar_expr )
COLLATE( string_expression,collation_specification )
MD5( expr )
SPACE( expr )
COLLATION( expression )
MD5_BINARY( expr )
SPLIT( expr1, expr2 )
COMPRESS( input,method )
MD5_HEX( expr )
SPLIT_PART( string,delimiter,part_number )
CONCAT( expr )
MD5_NUMBER_LOWER64( msg )
STARTSWITH( expr1, expr2 )
CONCAT_WS( expr1, expr2 )
MD5_NUMBER_UPPER64( msg )
STRTOK( string )
CONTAINS( expr1, expr2 )
OCTET_LENGTH( string )
STRTOK_TO_ARRAY( string )
DECOMPRESS_BINARY( input,method )
PARSE_IP( expr,type )
SUBSTR( base, start )
DECOMPRESS_STRING( input,method )
PARSE_URL( expr )
SUBSTRING( base, start )
EDITDISTANCE( expr1, expr2 )
POSITION( expr1, expr2 )
TRANSLATE( subject,sourceAlphabet,targetAlphabet )
ENDSWITH( expr1, expr2 )
REPEAT( expr1, expr2 )
TRIM( expr )
HASH( expr1 )
REPLACE( subject, pattern )
TRY_BASE64_DECODE_BINARY( input )
HEX_DECODE_BINARY( input )
REVERSE( expr )
TRY_BASE64_DECODE_STRING( input )
HEX_DECODE_STRING( input )
RIGHT( expr, len )
TRY_HEX_DECODE_BINARY( input )
HEX_ENCODE( input )
RIGHT( expr1, len )
TRY_HEX_DECODE_STRING( input )
ILIKE( subject,pattern )
RPAD( expr1, expr2 )
UNICODE( expr )
INITCAP( expr )
RPAD( expr1, expr2, expr3 )
UPPER( expr )
INSERT( expr1, pos, len, expr2 )
RTRIM( expr1 )
-

Window functions

Function
Function
Function
ANY_VALUE( expr )
BOOLXOR_AGG( expr )
RATIO_TO_REPORT( expr )
APPROX_COUNT_DISTINCT( expr )
CORR( y, x )
REGR_AVGX( y,x )
APPROX_PERCENTILE( expr,percentile )
COVAR_POP( y, x )
REGR_AVGY( y,x )
APPROX_PERCENTILE_ACCUMULATE( expr )
COVAR_SAMP( y, x )
REGR_COUNT( y,x )
APPROX_TOP_K( expr )
HASH_AGG( expr )
REGR_INTERCEPT( y,x )
APPROX_TOP_K_ACCUMULATE( expr,counters )
HLL( expr )
REGR_R2( y,x )
ARRAY_UNIQUE_AGG( column )
HLL_ACCUMULATE( expr )
REGR_SLOPE( y,x )
BITAND_AGG( expr )
HLL_COMBINE( state )
REGR_SXX( y,x )
BITOR_AGG( expr )
KURTOSIS( expr )
REGR_SXY( y,x )
BITXOR_AGG( expr )
KURTOSIS( expr )
REGR_SYY( y,x )
BOOLAND_AGG( expr )
MINHASH( k,expr )
-
BOOLOR_AGG( expr )
MODE( expr )
-
For information on functions and expression syntax, see SQL function reference in the Snowflake documentation.