Function Reference > Functions > Function quick reference
  

Function quick reference

The following table contains the syntax and a brief description of the functions that can be used in field expressions:
Note: 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 encrypted data to string format.
For more information, see AES_DECRYPT.
AES_ENCRYPT
Encoding
AES_ENCRYPT ( value, key )
Returns data in encrypted format.
For more information, see AES_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.
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.