Microsoft Azure Synapse SQL Connector

Back Next

Functions with Microsoft Azure Synapse SQL

You can push functions in a transformation to Microsoft Azure Synapse SQL using

SQL ELT optimization

When you use

SQL ELT optimization

, the Secure Agent converts the expression, filter, or aggregator in the transformation by determining equivalent functions in the database.

The following table summarizes the availability of SQL ELT functions when you configure

SQL ELT optimization

for a mapping that contains a

Microsoft Azure Synapse SQL

connection.

Function	Function	Function
ABS()	LAST_DAY()	SIGN()
ADD_TO_DATE()	LENGTH()	SIN()
ASCII()	LN()	SINH()
AVG()	LOG()	SOUNDEX()
CEIL()	LOWER()	SQRT()
CHR()	LPAD()	STDDEV()
CONCAT()	LTRIM()	SUBSTR()
COS()	MAKE_DATE_TIME()	SUM()
COUNT()	MAX()	SYSDATE()
DATE_DIFF()	MD5()	SYSTIMESTAMP()
DECODE()	MIN()	TAN()
EXP()	MOD()	TANH()
FIRST()¹	POWER()	TO_BIGINT()
FLOOR()	REG_MATCH()	TO_CHAR(DATE)
GET_DATE_PART()	REPLACECHR()	TO_CHAR(NUMBER)
IIF()	REPLACESTR()	TO_DATE()
IN()	ROUND(DATES)	TO_DECIMAL()
INSTR()	ROUND(NUMBER)	TO_FLOAT()
IS_DATE()	RPAD()	TO_INTEGER()
IS_NUMBER()	RTRIM()	TRUNC(DATE)
IS_SPACES()	SESSSTARTTIME()¹	TRUNC(NUMBER)
ISNULL()	SET_DATE_PART()	UPPER()
LAST()¹	SHA-256 ()¹	VARIANCE()
¹Doesn't apply to mappings in advanced mode.

The following table describes the syntax for the SQL ELT functions:

Function	Syntax	Description
ADD_TO_DATE	ADD_TO_DATE (date, format, amount)	Supported formats: yyyy mm dd hh mi ss ms us ns
DATE_DIFF	DATE_DIFF (date1, date2, format)	Supported formats: yyyy mm dd hh mi ss ms us ns
DECODE	value , first_search , first_result [, second_search , second_result ]...[, default ]	Value can contain any datatype except Binary. You can enter any valid expression. You cannot use true or false as values. You cannot add conditions to the search argument. For example, DECODE ( CONST_NAME, 'Five', 5, 'Pythagoras', 1.414213562, 'Archimedes', 3.141592654, 'Pi', 3.141592654 )
FIRST	FIRST (value)
GET_DATE_PART	GET_DATE_PART (date, format)	Supported formats: yyyy mm dd hh mi ss ms us ns
IIF	IIF (condition, value1 [,value2])
IN	Aggregator and Expression transformation IIF(IN(search_value, value1, [value2, ..., valueN,]), 'TRUE', 'FALSE') Filter transformation IN(search_value, value1, [value2, ..., valueN,])
INSTR	INSTR (string, search_value) INSTR (string, search_value [,start [,occurrence]])	When you use the INSTR (string, search_value) syntax, the search value must be enclosed in %. When you use the INSTR (string, search_value [,start [,occurrence]]) syntax, the search value must be a character expression that contains the sequence that you want to search. When you use the default values in start and occurrence parameters, the search value must be enclosed in %. Default value for start and occurrence parameters is 1.
IS_DATE	IS_DATE(value [,format]) Aggregator and Expression transformation IS_DATE(column name) IIF(IS_DATE(colname), value1 [,value2]) IIF(IS_DATE(colname)=0 or 1, value1 [,value2]) Filter and Router transformation IS_DATE(column name)=0 or 1 IIF ( IS_DATE(colname), 0 or 1, 0 or 1) IIF(IS_DATE(column name)=0 or 1, 0 or 1, 0 or 1)	Supported formats: dd mon yyyy hh:mi:ss mm/dd/yyyy mm/dd/yyyy hh24:mi:ss yyyy yyyy-mm-dd yyyy-mm-dd hh:mi:ss yyyy.mm.dd yyyy/mm/dd yyyymmdd mon dd yyyy hh:miAM Mon dd, yyyy mon dd yyyy hh:mi:ss:mmmAM mm-dd-yyyy dd mon yyyy hh:mi:ss:mmm yyyy-mm-dd hh:mi:ss.mmm mm/dd/yy hh:mi:ss AM yyyy-mm-ddThh:mi:ss.mmm yyyy-MM-ddThh:mm:ss.fffZ dd mon yyyy hh:mi:ss:mmmAM Date range: 1753-01-01 to 9999-12-31 .
ISNULL	Aggregator and Expression transformation IIF(ISNULL(colname), value1 [,value2]) IIF(ISNULL(colname)=0 or 1, value1 [,value2] ) Filter and Router transformation ISNULL(column_name) IIF(ISNULL(column name), 0 or 1, 0 or 1)
IS_NUMBER	Aggregator and Expression transformation IS_NUMBER(column name) IIF(IS_NUMBER(colname), value1 [,value2]) IIF(IS_NUMBER(colname)=0 or 1, value1 [,value2]) Filter and Router transformation IS_NUMBER(column name)=0 or 1 IIF(IS_NUMBER(column name), 0 or 1, 0 or 1) IIF(IS_NUMBER(column name)=0 or 1, 0 or 1, 0 or 1)
IS_SPACES	Aggregator and Expression transformation IS_SPACES(column name) IIF(IS_SPACES(colname), value1 [,value2]) IIF(IS_SPACES(colname)=0 or 1, value1 [,value2] Filter and Router transformation IS_SPACES(colname) = 0 or 1 IIF (IS_SPACES(colname), 0 or 1, 0 or 1) IIF(IS_SPACES(column name)=0 or 1, 0 or 1, 0 or 1)
LAST	LAST (value)
LAST_DAY	LAST_DAY (date)
LTRIM and RTRIM	LTRIM (string) RTRIM (string)	You can pass only a single argument in the LTRIM or RTRIM function.
REG_MATCH	REG_MATCH(subject, pattern)	Supported regular expression syntax used in pattern: . [^a-z] [a-zA-Z0-9] \d \s \w
ROUND(DATES)	ROUND( date [, format ] )	Supported formats: yy mm dd hh mi ss
TANH	Use the EXP() function in the following formula to calculate TANH : TANH(x) = (EXP(2 * x) - 1) / (EXP(2 * x) + 1)	Maximum value of x: 354 Minimum value of x: -354 If the value of x is greater than 354 or less than -354, use the follwing expression: CASE WHEN X > 354 THEN 1 WHEN X < -354 THEN -1 ELSE TANH(X) END
TO_BIGINT	TO_BIGINT (numeric value) TO_BIGINT truncates the decimal portion. To avoid truncation, use the ROUND function. TO_BIGINT (ROUND (numeric value [,0]))	You can pass only a single argument in the function.
TO_CHAR (Date)	TO_CHAR (date [,format]) Specify a supported format. The format defines the format of the return value, not the format for the values in the date argument.	Supported formats: mm.yyyy dd/mm/yyyy dd.mm.yyyy dd-mm-yyyy dd mon yyyy mm/dd/yyyy hh:mi:ss yyyy.mm.dd yyyy/mm/dd yyyymmdd yyyy-mm-dd hh:mi:ss mm/dd/yyyy hh24:mi:ss
TO_DATE	TO_DATE (string [,format]) The format must match the parts of the string argument.	Supported formats: dd/mm/yyyy dd.mm.yyyy dd-mm-yyyy dd mon yyyy mm/dd/yyyy hh:mi:ss yyyy yyyymm yyyymmdd yyyy-mm-dd yyyy.mm.dd yyyy/mm/dd yyyy-mm-dd hh:mi:ss
SUBSTR	SUBSTR (string, start [,length ])	The start argument must be a positive number. The length must be an integer greater than 0.

Rename Saved Search

Table of Contents

Microsoft Azure Synapse SQL Connector

Microsoft Azure Synapse SQL Connector

Functions with Microsoft Azure Synapse SQL

Functions with Microsoft Azure Synapse SQL