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. |
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. |