Preface
Function reference
- Function components
Constants
Operators
Dates
Functions
- Function overview
  - Aggregate functions
    - Filter conditions
  - Conversion functions
  - Data cleansing functions
  - Date functions
  - Encoding functions
  - Financial functions
  - Horizontal expansion functions
  - Numeric functions
  - Scientific functions
  - Special functions
  - String functions
  - Test functions
  - Window functions
    - Aggregate functions as window functions
    - Nested aggregate functions as window functions
- Function quick reference
- Functions in advanced mode
- %OPR_CONCAT%
- %OPR_CONCATDELIM%
- %OPR_IIF%
- %OPR_SUM%
- ABORT
- ABS
- ADD_TO_DATE
- AES_DECRYPT
- AES_ENCRYPT
- AES_GCM_DECRYPT
- AES_GCM_ENCRYPT
- ASCII
- AVG
- CEIL
- CHOOSE
- CHR
- CHRCODE
- COMPRESS
- CONCAT
- CONVERT_BASE
- COS
- COSH
- COUNT
- CRC32
- CUME
- DATE_COMPARE
- DATE_DIFF
- DEC_BASE64
- DECODE
- DECOMPRESS
- ENC_BASE64
- ERROR
- EXP
- FIRST
- FLOOR
- FV
- GET_DATE_PART
- GREATEST
- IIF
- IN
- INDEXOF
- INITCAP
- INSTR
- IS_DATE
- IS_NUMBER
- IS_SPACES
- ISNULL
- LAG
- LAST
- LAST_DAY
- LEAD
- LEAST
- LENGTH
- LN
- LOG
- LOWER
- LPAD
- LTRIM
- MAKE_DATE_TIME
- MAX (Dates)
- MAX (Numbers)
- MAX (String)
- MD5
- MEDIAN
- METAPHONE
- MIN (Dates)
- MIN (Numbers)
- MIN (String)
- MOD
- MOVINGAVG
- MOVINGSUM
- NPER
- PERCENTILE
- PMT
- POWER
- PV
- RAND
- RATE
- REG_EXTRACT
- REG_MATCH
- REG_REPLACE
- REPLACECHR
- REPLACESTR
- REVERSE
- ROUND (Dates)
- ROUND (Numbers)
- RPAD
- RTRIM
- SET_DATE_PART
- SETCOUNTVARIABLE
- SETMAXVARIABLE
- SETMINVARIABLE
- SETVARIABLE
  - Differences between SETVARIABLE, SETMAXVARIABLE, and SETMINVARIABLE
- SHA256
- SIGN
- SIN
- SINH
- SOUNDEX
- SQRT
- STDDEV
- SUBSTR
- SUM
- SYSTIMESTAMP
- TAN
- TANH
- TO_BIGINT
- TO_CHAR (Dates)
- TO_CHAR (Numbers)
- TO_DATE
- TO_DECIMAL
- TO_FLOAT
- TO_INTEGER
- TRUNC (Dates)
- TRUNC (Numbers)
- UPPER
- UUID_STRING
- VARIANCE
System variables
Datatype reference

Function Reference

Back Next

Function quick reference

The following table contains the syntax and a brief description of the functions that can be used in field expressions:

The functions that you can use depend on the mapping type.

Function	Function type	Syntax	Description
%OPR_CONCAT%	Horizontal Expansion	%OPR_CONCAT[ macro_input_field ]%	Uses the CONCAT function and expands an expression in an expression macro to concatenate multiple fields. For more information, see %OPR_CONCAT%
%OPR_CONCATDELIM%	Horizontal Expansion	%OPR_CONCATDELIM[ macro_input_field ]%	Uses the CONCAT function and expands an expression in an expression macro to concatenate multiple fields, and adds a comma delimiter. For more information, see %OPR_CONCATDELIM%
%OPR_IIF%	Horizontal Expansion	%OPR_IIF[ condition , macro_input_field [,value ] ]%	Uses the IIF function and expands an expression in an expression macro to evaluate a set of IIF statements. For more information, see %OPR_IIF%
%OPR_SUM%	Horizontal Expansion	%OPR_SUM[ macro_input_field [, filter_condition ] ]%	Uses the SUM function and expands an expression in an expression macro to return the sum of all fields. For more information, see %OPR_SUM%
ABORT	Special	ABORT( string )	Stops the session and issues a specified error message. For more information, see ABORT.
ABS	Numeric	ABS( numeric_value )	Returns the absolute value of a numeric value. For more information, see ABS.
ADD_TO_DATE	Data Cleansing, Date	ADD_TO_DATE( date , format , amount )	Adds a specified amount to one part of a date/time value, and returns a date in the same format as the specified date. If you do not specify the year as YYYY, Data Integration assumes the date is in the current century. For more information, see ADD_TO_DATE.
AES_DECRYPT	Encoding	AES_DECRYPT ( value, key )	Returns the decrypted value in string format, after performing AES-ECB decryption on the input value. For more information, see AES_DECRYPT.
AES_ENCRYPT	Encoding	AES_ENCRYPT ( value, key )	Returns binary data in encrypted format, after performing AES-ECB encryption on the input value. For more information, see AES_ENCRYPT.
AES_GCM_DECRYPT	Encoding	AES_GCM_DECRYPT ( value, init_vector, key [, keysize ] )	Returns plaintext, a decrypted value as a string, after performing AES-GCM decryption on an input value with the given initialization vector and key. For more information, see AES_GCM_DECRYPT.
AES_GCM_ENCRYPT	Encoding	AES_GCM_ENCRYPT ( value, init_vector, key [, keysize ] )	Returns ciphertext as a binary value after performing AES-GCM encryption on an input value with the given initialization vector and key. The ciphertext is encrypted plaintext. For more information, see AES_GCM_ENCRYPT.
ASCII	String	ASCII ( string )	Returns the numeric ASCII value of the first character of the string passed to the function. This function is identical in behavior to the CHRCODE function. If you use the ASCII function in existing expressions, it will still work correctly. However, when you create new expressions, use the CHRCODE function instead of the ASCII function. For more information, see ASCII.
AVG	Aggregate	AVG ( numeric_value [, filter_condition ] )	Returns the average of all values in a group of rows. For more information, see AVG.
CEIL	Numeric	CEIL ( numeric_value )	Returns the smallest integer greater than or equal to the specified numeric value. For more information, see CEIL.
CHOOSE	String	CHOOSE( index, string1 , [string2 , ..., stringN ] )	Chooses a string from a list of strings based on a given position. For more information, see CHOOSE.
CHR	String	CHR( numeric_value )	Returns the ASCII character corresponding to the specified numeric value. For more information, see CHR.
CHRCODE	String	CHRCODE( string )	Returns the numeric ASCII value of the first character of the string passed to the function. This function is identical in behavior to the ASCII function. For more information, see CHRCODE.
COMPRESS	Encoding	COMPRESS( value )	Compresses data using the zlib compression algorithm. For more information, see COMPRESS.
CONCAT	String	CONCAT( first_string , second_string )	Concatenates two strings. For more information, see CONCAT.
CONVERT_BASE	Numeric	CONVERT_BASE( value, source_base, dest_base )	Converts a number from one base value to another base value. For more information, see CONVERT_BASE.
COS	Scientific	COS( numeric_value )	Returns the cosine of a numeric value (expressed in radians). For more information, see COS.
COSH	Scientific	COSH( numeric_value )	Returns the hyperbolic cosine of a numeric value (expressed in radians). For more information, see COSH.
COUNT	Aggregate	COUNT( value [, filter_condition ] ) or COUNT( * [, filter_condition ] )	Returns the number of rows that have non-null values in a group. For more information, see COUNT.
CRC32	Encoding	CRC32( value )	Returns a 32-bit Cyclic Redundancy Check (CRC32) value. For more information, see CRC32.
CUME	Numeric	CUME( numeric_value [, filter_condition ] )	Returns a running total. For more information, see CUME.
DATE_COMPARE	Data Cleansing, Date	DATE_COMPARE( date1 , date2 )	Returns a value indicating the earlier of two dates. For more information, see DATE_COMPARE.
DATE_DIFF	Data Cleansing, Date	DATE_DIFF( date1, date2, format )	Returns the length of time between two dates, measured in the specified increment (years, months, days, hours, minutes, or seconds). For more information, see DATE_DIFF.
DEC_BASE64	Encoding	DEC_BASE64( value )	Decodes the value and returns a string with the binary data representation of the data. For more information, see DEC_BASE64.
DECODE	Special	DECODE( value, first_search, first_result [, second_search , second_result ]…[, default ] )	Searches a column for the specified value. For more information, see DECODE .
DECOMPRESS	Encoding	DECOMPRESS( value, precision )	Decompresses data using the zlib compression algorithm. For more information, see DECOMPRESS.
ENC_BASE64	Encoding	ENC_BASE64( value )	Encodes data by converting binary data to string data using Multipurpose Internet Mail Extensions (MIME) encoding. For more information, see ENC_BASE64.
ERROR	Special	ERROR( string )	Causes the Data Integration to skip a row. It writes the row into the error rows file with the specified error message. For more information, see ERROR.
EXP	Numeric	EXP( exponent )	Returns e raised to the specified power (exponent), where e=2.71828183. For more information, see EXP.
FIRST	Aggregate	FIRST( value [, filter_condition ] )	Returns the first value found within a field or group. For more information, see FIRST.
FLOOR	Numeric	FLOOR( numeric_value )	Returns the largest integer less than or equal to the specified numeric value. For more information, see FLOOR.
FV	Financial	FV( rate, terms, payment [, present value, type ] )	Returns the future value of an investment, where you make periodic, constant payments and the investment earns a constant interest rate. For more information, see FV.
GET_DATE_PART	Date, Data Cleansing	GET_DATE_PART( date, format )	Returns the specified part of a date as an integer value, based on the default date format of MM/DD/YYYY HH24:MI:SS. For more information, see GET_DATE_PART.
GREATEST	Data Cleansing	GREATEST( value1 , [value2 , ..., valueN ,] CaseFlag )	Returns the greatest value from a list of input values. For more information, see GREATEST.
IIF	Special	IIF( condition, value2 [, value2 ] )	Returns one of two values you specify, based on the results of a condition. For more information, see IIF.
IN	Data Cleansing	IN( valueToSearch , value1 , [value2 , ..., valueN ,] CaseFlag )	Matches input data to a list of values. For more information, see IN.
INDEXOF	String	INDEXOF( valueToSearch, string1, [string2 , ..., stringN ,] CaseFlag )	Finds the index of a string among a list of strings. For more information, see INDEXOF.
INITCAP	String	INITCAP( string )	Capitalizes the first letter in each word of a string and converts all other letters to lowercase. For more information, see INITCAP.
INSTR	String, Data Cleansing	INSTR( string , search_value [, start [, occurrence ] ] )	Returns the position of a character set in a string, counting from left to right. For more information, see INSTR.
IS_DATE	Data Cleansing, Test	IS_DATE( value )	Returns whether a value is a valid date. For more information, see IS_DATE.
IS_NUMBER	Data Cleansing, Test	IS_NUMBER( value )	Returns whether a string is a valid number. For more information, see IS_NUMBER.
IS_SPACES	Data Cleansing, Test	IS_SPACES( value )	Returns whether a value consists entirely of spaces. For more information, see IS_SPACES.
ISNULL	Data Cleansing, Test	ISNULL( value )	Returns whether a value is NULL. For more information, see ISNULL.
LAG	Window	LAG( field_name, offset, default_value )	Returns the value from a preceding row. For more information, see LAG.
LAST	Aggregate	LAST( value [, filter_condition ] )	Returns the last row in the selected field. For more information, see LAST.
LAST_DAY	Data Cleansing, Date	LAST_DAY( date )	Returns the date of the last day of the month for each date in a column. For more information, see LAST_DAY.
LEAD	Window	LEAD( field_name, offset, default_value )	Returns the value from a following row. For more information, see LEAD.
LEAST	Data Cleansing	LEAST( value1 , [value2 , ..., valueN ,] CaseFlag )	Returns the smallest value from a list of input values. For more information, see LEAST.
LENGTH	String	LENGTH( string )	Returns the number of characters in a string, including trailing blanks. For more information, see LENGTH.
LN	Numeric	LN( numeric_value )	Returns the natural logarithm of a numeric value. For more information, see LN.
LOG	Numeric	LOG( base, exponent )	Returns the logarithm of a numeric value. For more information, see LOG.
LOWER	String	LOWER( string )	Converts uppercase string characters to lowercase. For more information, see LOWER.
LPAD	String	LPAD( first_string , length [, second_string ] )	Adds a set of blanks or characters to the beginning of a string to set a string to a specified length. For more information, see LPAD.
LTRIM	String, Data Cleansing	LTRIM( string [, trim_set ] )	Removes blanks or characters from the beginning of a string. For more information, see LTRIM.
MAKE_DATE_TIME	Data Cleansing, Date	MAKE_DATE_TIME( year, month, day, hour, minute, second )	Returns the date and time based on the input values. For more information, see MAKE_DATE_TIME.
MAX (Dates)	Aggregate	MAX( date [, filter_condition ] )	Returns the latest date found within a field or group. For more information, see MAX (Dates).
MAX (Numbers)	Aggregate	MAX( numeric_value [, filter_condition ] )	Returns the maximum numeric value found within a field or group. For more information, see MAX (Numbers).
MAX (String)	Aggregate	MAX( string [, filter_condition ] )	Returns the highest string value found within a field or group. For more information, see MAX (String).
MD5	Encoding	MD5( value )	Calculates the checksum of the input value. The function uses Message-Digest algorithm 5 (MD5). For more information, see MD5.
MEDIAN	Aggregate	MEDIAN( numeric_value [, filter_condition ] )	Returns the median of all values in a selected field. For more information, see Median.
METAPHONE	Data Cleansing	METAPHONE( string [, length ] )	Encodes characters of the English language alphabet (A-Z). For more information, see METAPHONE.
MIN (Dates)	Aggregate	MIN( date [, filter_condition ] )	Returns the earliest date found in a field or group. For more information, see MIN (Dates).
MIN (Numbers)	Aggregate	MIN( date [, filter_condition ] )	Returns the smallest numeric value found in a field or group. For more information, see MIN (Numbers).
MIN (String)	Aggregate	MIN( string [, filter_condition ] )	Returns the lowest string value found in a field or group. For more information, see MIN (String).
MOD	Numeric	MOD( numeric_value, divisor )	Returns the remainder of a division calculation. For more information, see MOD.
MOVINGAVG	Numeric	MOVINGAVG( numeric_value , rowset [, filter_condition ] )	Returns the average (row-by-row) of a specified set of rows. For more information, see MOVINGAVG.
MOVINGSUM	Numeric	MOVINGSUM( numeric_value , rowset [, filter_condition ] )	Returns the sum (row-by-row) of a specified set of rows. For more information, see MOVINGSUM.
NPER	Financial	NPER( rate, present value, payment [, future value, type ])	Returns the number of periods for an investment based on a constant interest rate and periodic, constant payments. For more information, see NPER.
PERCENTILE	Aggregate	PERCENTILE( numeric_value , percentile [, filter_condition ] )	Calculates the value that falls at a given percentile in a group of numbers. For more information, see PERCENTILE.
PMT	Financial	PMT( Rate, terms, present value [, future value, type ] )	Returns the payment for a loan based on constant payments and a constant interest rate. For more information, see PMT.
POWER	Numeric	POWER( base, exponent )	Returns a value raised to the specified exponent. For more information, see POWER.
PV	Financial	PV( Rate, terms, payment [, future value, type ] )	Returns the present value of an investment. For more information, see PV.
RAND	Numeric	RAND( seed )	Returns a random number between 0 and 1. For more information, see RAND.
RATE	Financial	RATE( terms, payment, present value [, future value, type ] )	Returns the interest rate earned per period by a security. For more information, see RATE.
REG_EXTRACT	Data Cleansing	REG_EXTRACT( subject , pattern, subPatternNum )	Extracts subpatterns of a regular expression within an input value. For more information, see REG_EXTRACT.
REG_MATCH	Data Cleansing	REG_MATCH( subject , pattern )	Returns whether a value matches a regular expression pattern. For more information, see REG_MATCH.
REG_REPLACE	Data Cleansing	REG_REPLACE( subject, pattern, replace, numReplacements )	Replaces characters in a string with a another character pattern. For more information, see REG_REPLACE.
REPLACECHR	String, Data Cleansing	REPLACECHR( CaseFlag, InputString, OldCharSet, NewChar )	Replaces characters in a string with a single character or no character. For more information, see REPLACECHR.
REPLACESTR	String, Data Cleansing	REPLACESTR ( InputString , OldString1 , [OldString2, ... OldStringN,] NewString )	Replaces characters in a string with a single character, multiple characters, or no character. For more information, see REPLACESTR .
REVERSE	String	REVERSE( string )	Reverses the input string. For more information, see REVERSE.
ROUND	Data Cleansing, Date, Numeric	ROUND( date [, format ] ) or ROUND( numeric_value [, precision ] )	For data cleansing, rounds one part of a date. For numeric values, rounds numbers to a specified digit. For more information, see ROUND (Dates) or ROUND (Numbers).
RPAD	String	RPAD( first_string , length [, second_string ] )	Converts a string to a specified length by adding blanks or characters to the end of the string. For more information, see RPAD.
RTRIM	String, Data Cleansing	RTRIM( string [, trim_set ] )	Removes blanks or characters from the end of a string. For more information, see RTRIM.
SET_DATE_PART	Data Cleansing, Date	SET_DATE_PART( date, format, value )	Sets one part of a date/time value to a specified value. For more information, see SET_DATE_PART.
SETCOUNTVARIABLE	Special	SETCOUNTVARIABLE( $$Variable )	Counts the rows evaluated by the function and increments the current value of an in-out parameter based on the count. For more information, see SETCOUNTVARIABLE.
SETMAXVARIABLE	Special	SETMAXVARIABLE( $$Variable , value )	Sets the current value of an in-out parameter to the higher of two values: the current value of the parameter or the value you specify. For more information, see SETMAXVARIABLE.
SETMINVARIABLE	Special	SETMINVARIABLE( $$Variable , value )	Sets the current value of an in-out parameter to the lower of two values: the current value of the parameter or the value you specify. For more information, see SETMINVARIABLE.
SETVARIABLE	Special	SETVARIABLE( $$Variable , value )	Sets the current value of an in-out parameter to a value you specify. For more information, see SETVARIABLE.
SHA256	Encoding	SHA256( value )	Returns the SHA-256 digest of the input value. For more information, see SHA256.
SIGN	Numeric	SIGN( numeric_value )	Indicates whether a numeric value is positive, negative, or 0. For more information, see SIGN.
SIN	Scientific	SIN( numeric_value )	Returns the sin of a numeric value expressed in radians. For more information, see SIN.
SINH	Scientific	SINH( numeric_value )	Returns the hyperbolic sin of a numeric value expressed in radians. For more information, see SINH.
SOUNDEX	Data Cleansing	SOUNDEX( string )	Encodes a string value into a four-character string. For more information, see SOUNDEX.
SQRT	Numeric	SQRT( numeric_value )	Returns the square root of a positive numeric value. For more information, see SQRT.
STDDEV	Aggregate	STDDEV( numeric_value [,filter_condition ] )	Returns the standard deviation of the numeric values you pass to this function. For more information, see STDDEV.
SUBSTR	String, Data Cleansing	SUBSTR( string , start [, length ] )	Returns a portion of a string. For more information, see SUBSTR.
SUM	Aggregate	SUM( numeric_value [, filter_condition ] )	Returns the sum of all values in the selected field. For more information, see SUM.
SYSTIMESTAMP	Date	SYSTIMESTAMP( [format ] )	Returns the current date and time with precision to the nanosecond of the system that hosts the Secure Agent that starts the task. For more information, see SYSTIMESTAMP.
TAN	Scientific	TAN( numeric_value )	Returns the tangent of a numeric value expressed in radians. For more information, see TAN.
TANH	Scientific	TANH( numeric_value )	Returns the hyperbolic tangent of a numeric value expressed in radians. For more information, see TANH.
TO_BIGINT	Conversion, Data Cleansing	TO_BIGINT( value [, flag ] )	Converts a string or numeric value to a bigint value. For more information, see TO_BIGINT.
TO_CHAR	Conversion, Data Cleansing	TO_CHAR( date [, format ] ) or TO_CHAR( numeric_value )	Converts dates or numeric values to text strings. For more information, see TO_CHAR(Dates) or TO_CHAR(Numbers).
TO_DATE	Conversion, Data Cleansing	TO_DATE( string [, format ] )	Converts a character string to a date datatype in the same format as the character string. For conversion, you must specify the date format if the string is not in the mm/dd/yyyy hh:mi:ss format. For more information, see TO_DATE. For more information about date formats, see Dates.
TO_DECIMAL	Conversion, Data Cleansing	TO_DECIMAL( value [, scale ] )	Converts any value (except binary) to a decimal. For more information, see TO_DECIMAL.
TO_FLOAT	Conversion, Data Cleansing	TO_FLOAT( value )	Converts any value (except binary) to a double-precision floating point number (the Double datatype). For more information, see TO_FLOAT.
TO_INTEGER	Conversion, Data Cleansing	TO_INTEGER( value )	Converts any value (except binary) to an integer by rounding the decimal portion of a value. For more information, see TO_INTEGER.
TRUNC	Data Cleansing, Date, Numeric	TRUNC( date [, format ] ) or TRUNC( numeric_value [, precision ])	Truncates dates to a specific year, month, day, hour, or minute. Truncates numeric values to a specific digit. For more information, see TRUNC(Dates) or TRUNC(Numbers).
UPPER	String	UPPER( string )	Converts lowercase string characters to uppercase. For more information, see UPPER.
VARIANCE	Aggregate	VARIANCE( numeric_value [, filter_condition ] )	Returns the variance of a value you pass to it. For more information, see VARIANCE.