These function types are available for transforming your data in Designer Cloud. The type of data determines the functions you can use.
Visit Supported Data Types for more information.
Use this guide to quickly reference functions that you can use in the Expression Editor in Designer Cloud. If you need more information and examples, visit the category-specific pages linked in each section.
Note
Not all functions are supported between Standard mode and Cloud Native mode tools. For a list of supported functions, go to the respective Standard mode and Could Native mode function lists found on the Formula tool page.
Conditional functions let you perform an action or calculation using an IF statement. For parameters and examples, visit the Workflow Conditional Functions article.
IF c THEN t ELSE f ENDIF
: Returns t if the condition c is true, else returns f.
IF c THEN t1 ELSEIF c2 THEN t2 ELSE f ENDIFIF c THEN t ELSE f ENDIF
: Returns t1 if c is true, else returns t2 if condition c2 is true, else returns f.
IIF(bool, x, y)
: Returns x if bool is true, else returns y.
Switch(Value,Default,Case1,Result1,...,CaseN,ResultN)
: Compares a value against a list of cases and returns the corresponding result.
Conversion functions convert numbers to strings or strings to numbers. For parameters and examples, visit the Workflow Conversion Functions article.
BinToInt(s)
: Converts the binary string s to an integer (Limited to 53 bits).
CharFromInt(x)
: Returns the Unicode® character that matches the input number x.
CharToInt(s)
: Returns the number that matches the input Unicode® character s.
ConvertFromCodePage(s, codePage)
: Translates text from a code page to Unicode. To learn more about code pages, go to the Code Pages documentation.
ConvertToCodePage(s, codePage)
: Translates text from Unicode encoding to a specific code page. To learn more about code pages, visit the Code Pages documentation.
HexToNumber(x)
: Converts a HEX string to a number (Limited to 53 bits).
IntToBin(x)
: Converts x to a binary string.
IntToHex(x)
: Converts x to a hexadecimal string.
ToDegrees(x)
: Converts a numeric radian value (x) to degrees via the (x)rad × 180/π calculation. Please note that x must be a numeric value and can’t include any radian symbol (rad).
ToNumber(x, [bIgnoreErrors], [keepNulls], [decimalSeparator])
: Converts a string (x), to a number.
ToRadians(x)
: Converts a numeric degree value (x) to radians via the (x)° × π/180 calculation. Please note that x must be a numeric value and can’t include the degree symbol (°).
ToString(x, numDec, [addThousandsSeparator], [decimalSeparator])
: Converts a numeric parameter (x) to a string using numDec decimal places. Default selection uses a period as the decimal separator.
UnicodeNormalize(String, Form)
: Converts text data in the provided string into a standardized Unicode form. Use the required Form parameter (case-insensitive) to define the normalization form. You must manually enter one of the below normalization form options (this parameter can't be populated via an upstream field.
DateTime functions let you perform an action or calculation on a date and time value. For parameters and examples, visit the DateTime Functions article.
DateTimeAdd(dt,i,u)
: Adds a specific interval to a date-time value.
DateTimeDay(dt)
: Returns the numeric value for the day of the month in a date-time value.
DateTimeDiff(dt1,dt2,u)
: Subtracts the second argument from the first and returns it as an integer difference. The duration is returned as a number, not a string, in the specified time units.
DateTimeFirstOfMonth()
: Returns the first day of the month, at midnight.
DateTimeFormat(dt, f)
: Converts date-time data df to another specified format f.
DateTimeHour(dt)
: Returns the hour portion of the time in a date-time value.
DateTimeLastOfMonth()
: Returns the last day of the current month, with the clock set to one second before the end of the day (23:59:59).
DateTimeMinutes(dt)
: Returns the minutes portion of the time in a date-time value.
DateTimeMonth(dt)
: Returns the numeric value for the month in a date-time value.
DateTimeNow()
: Returns the current system date and time.
DateTimeParse(dt, f)
: Converts a date string with the specified format and language to the standard ISO format (yyyy-mm-dd HH:MM:SS).
DateTimeQuarter(dt,[Q1Start])
: Returns the numeric value for the quarter of the year in which a date-time (YYYY-MM-DD) value falls. Use the optional numeric parameter to indicate the start month for the first quarter (Q1).
DateTimeSeconds(dt)
: Returns the seconds portion of the time in a date-time value.
DateTimeToday()
: Returns today’s date.
DateTimeTrim(dt, f)
: Removes unwanted portions of a date-time value and returns the modified date-time value.
DateTimeYear(dt)
: Returns the numeric value for the year in a date-time value.
ToDate(x)
: Converts a string, number, or date-time to a date.
DateTimeTrim(ToDateTime(x), t)
: Converts a string, number, or date value to a date-time.
A finance function applies financial algorithms or mathematical calculations. Learn more in the Workflow Finance Functions article.
FinanceCAGR(BeginningValue, EndingValue, NumYears)
: Calculates Compound Annual Growth Rate: The geometric mean growth rate on an annualized basis.
FinanceEffectiveRate(NominalRate, PaymentsPerYear)
: Calculates Effective Annual Interest Rate: The interest rate on a loan or financial product restated from the nominal interest rate as an interest rate with an annual compound interest payable in arrears.
FinanceFV(Rate, NumPayments, PaymentAmount, PresentValue, PayAtPeriodBegin)
: Calculates Future Value of an Investment: The value of an asset at a specified time in the future assuming a certain interest rate or rate of return.
FinanceFVSchedule(Principle, Year1Rate, Year2Rate)
: Calculates Future Value Schedule: The future value of an initial principal after applying a series of interest rates to an investment.
FinanceIRR(Value1, Value2)
: Calculates Internal Rate of Return, which is the interest rate at which the costs of the investment lead to the benefits of the investment. This means that all gains from the investment are inherent to the time value of money and that the investment has a zero net present value at this interest rate.
FinanceMIRR(FinanceRate, ReinvestRate, Value1, Value2)
: Calculates Modified Internal Rate of Return: A modification of the internal rate of return that aims to resolve some problems with the IRR. The MIRR is a financial measure of an investment's attractiveness.
FinanceMXIRR(FinanceRate, ReinvestRate, Value1, Date1, Value2, Date2)
: Calculates Modified Internal Rate of Return of an investment with dates.
FinanceNominalRate(EffectiveRate, PaymentsPerYear)
: Calculates Nominal Annual Interest Rate: An interest rate is called nominal if the frequency of compounding (such as a month) is not identical to the basic time unit (normally a year).
FinanceNPER(Rate, PaymentAmount, PresentValue, FutureValue, PayAtPeriodBegin)
: Calculates the Number of periods for an investment or loan.
FinanceNPV(Rate, Value1, Value2)
: Calculates Net Present Value of an investment: Measures the excess or shortfall of cash flows, in present value terms, once financing charges are met.
FinancePMT(Rate, NumPayments, PresentValue, FutureValue, PayAtPeriodBegin)
: Calculates payments on a loan.
FinancePV(Rate, NumPayments, PaymentAmount, FutureValue, PayAtPeriodBegin)
: Calculates Present Value of an investment: The value on a given date of a future payment, or series of future payments, discounted to reflect the time value of money and other factors such as investment risk.
FinanceRate(NumPayments, PaymentAmount, PresentValue, FutureValue, PayAtPeriodBegin)
: Calculates the interest rate (per period).
FinanceXIRR(Value1, Date1, Value2, Date2)
: Calculates the Internal Rate of Return of an investment with dates.
FinanceXNPV(Rate, Value1, Date1, Value2, Date2)
: Calculates the Net Present Value of an investment with dates.
A math function performs mathematical calculations. For parameters and examples, visit the Workflow Math Functions article.
ABS(x)
: Returns the absolute value of x. It is the distance between 0 and a number. The value is always positive.
ACOS(x)
: Returns the arccosine, or inverse cosine, of a number (x). The arccosine is the angle whose cosine is (x).
ASIN(x)
: Returns the arcsine, or inverse sine, of a number (x). The arcsine is the angle whose sine is (x).
ATAN(x)
: Returns the arctangent, or inverse tangent, of a number (x). The arctangent is the angle whose tangent is (x). The returned angle is given in radians in the range -pi/2 to pi/2.
ATAN2(y, x)
: Returns the arctangent, or inverse tangent, of y and x (Arc - Tangent of (y/x)).
Average(n1, ...)
: Returns the average of a list of numbers. NULL values are treated as 0 and taken into consideration in the calculation of the average value.
AverageNonNull(n1, ...)
: Returns the average of a list of numbers. Null values are excluded from the calculation. The calculation only works with Numeric data types.
.CEIL(x, [mult])
: Returns a numeric value (x) raised to the lowest multiple of (mult) that is greater than or equal to x. The (mult) parameter must be a number greater than 0. If (mult) is not specified, the function returns the smallest integer greater than or equal to (x).
COS(x)
: Returns the cosine of a given angle (x). x must be in radians.
COSH(x)
: Returns the hyperbolic cosine of a number (x).
EXP(x)
: Returns e raised to the x power.
Factorial(x)
: Returns the factorial of a numeric value (x) via the (n! = n x (n–1) x … x 1) calculation. For example, the factorial of 4 is calculated as such: 4! = 4 x 3 x 2 x 1 = 24.
FLOOR(x, [mult])
: Returns a numeric value (x) lowered to the highest multiple of (mult) that is less than or equal to x. The (mult) parameter must be a number greater than 0. If (mult) is not specified, the function returns the largest integer less than or equal to (x), as shown in the first example below.
LOG(x)
: Returns the natural logarithm of a number (x). x should be a positive number.
LOG10(x)
: Returns the base-10 logarithm of a number (x). x should be a positive number.
Median(...)
: Calculates the median of 1 or more (possibly unsorted) values.
Mod(n,d)
: Modulo of a number (n) divided by a number (d) (integer operation).
PI()
: Returns the value of the constant PI to 15 digits of accuracy.
POW(x, e)
: Returns a number (x) raised to the (e) power.
RAND()
: Returns a random number greater than or equal to 0 and less than 1.
RandInt(n)
: Returns a random integer between 0 and the specified parameter (n).
Round(x, mult)
: Returns x rounded to the nearest multiple of the number specified in mult.
SIN(x)
: Returns the sine of a given angle (x). x must be in radians.
SINH(x
): Returns the hyperbolic sine of a number (x).
SmartRound(x)
: Returns a number (x) rounded to the nearest multiple of a value determined dynamically based on the size of (x).
SQRT(x)
: Returns the square root of a number (x).
TAN(x)
: Returns the tangent of a given angle (x). x must be in radians.
TANH(x)
: Returns the hyperbolic tangent of a number (x).
An integer function operates on 1 or more bit patterns or binary numerals at the level of their individual bits.
BinaryAnd(n,m)
: Returns a binary of (n) and (m). The result is 1 if both n and m are 1, and 0 otherwise. If 0 is equated with False, and 1 with True, the BinaryAnd operation works like a logical And. Note the absence of the decimal point on the result values. The result is an integer, not a float value.
BinaryNot(n)
: Returns a Binary Not of (n). Numbers are treated as 64-bit, two's complement numbers.
BinaryOr(n,m)
: Returns a Binary Or of (n) and (m).
BinaryXOr(n,m)
: Returns a Binary XOr of (n) and (m).
ShiftLeft(n,b)
: Left shifts (n) (as integer) by (b) bits.
ShiftRight(n,b)
: Right shifts (n) (as integer) by (b) bits.
A minimum or maximum function finds the smallest and largest value of a set of values. Learn more in the Min/Max Functions article.
BETWEEN(x, min, max)
: Tests if a value (x) is between 2 other values (min and max), inclusive of min and max. Returns TRUE if x is between min and max. Returns FALSE if x is not between min and max.
Bound(x, min, max)
: If (x < min) returns min, else if (x > max) return max, else returns (x).
Max(v0, v1,..., vn)
: Returns the maximum value from the list.
MaxIDX(v0, v1,..., vn)
: Returns the 0-based index of the maximum value from the list.
Min(v0, v1,..., vn)
: Returns the minimum value from the list.
MinIDX(v0, v1,..., vn)
: Returns the 0-based index of the minimum value from the list.
An operator is a character that represents an action. Use an arithmetic operator to perform mathematical calculations or a Boolean operator to work with true/false values. Learn more in the Workflow Operators article.
+
: Addition.
AND
: Combines 2 boolean values. The result is True if both of the combined values are True, False if either of them is False but not NULL, and NULL if either of them is NULL.
&&
: Combines 2 boolean values. The result is True if both of the combined values are True, False if either of them is False but not NULL, and NULL if either of them is NULL.
NOT
: Accepts 1 input. If that input is True, it returns False. If that input is False, it returns True. If that input is NULL, it returns NULL.
!
: Accepts 1 input. If that input is True, it returns False. If that input is False, it returns True. If that input is NULL, it returns NULL.
OR
: Combines 2 boolean values. The result is True if either (or both) of the two values is true. Returns NULL if any of the values is NULL.
||
: Combines 2 boolean values. The result is True if either (or both) of the two values is true. Returns NULL if any of the values is NULL.
/
: Division.
=
: Equal to.
>
: Greater than.
>=
: Greater than or equal.
<
: Less than.
<=
: Less than or equal.
*
: Multiplication.
!=
: Not Equal To
-
: Subtraction.
IN
: Test value in list.
NOT IN
: Test value not in list.
These functions perform a variety of specialized actions and can be used with all data types. For parameters and examples, visit the Workflow Specialized Functions article.
Coalesce(v1,v2,v3,…,vn)
: Returns the first non-null value.
EscapeXMLMetacharacters(String)
: Replaces XML metacharacters with their escaped versions.
GetVal(index, v0,...vn)
: Returns the value (v0, ..., vn) specified by the 0-based index.
Message(messageType, message, returnValue)
: Use with a conditional statement to output a message to the message log and update column data with a specified value when a condition is met.
NULL()
: Returns a Null value.
RangeMedian(...)
: Calculates the median from a series of aggregated ranges. Go to Range Median for more information.
Soundex(String)
: Returns the Soundex of String.
Soundex_Digits(String)
: Returns the first 4 digits or the Soundex if none.
TOPNIDX(N, v0, v1, ..., vn)
: Returns the 0-based original index position of the Nth from the maximum value upon sorting the indexed fields (v0 to vn) in descending order. Null values are ignored and N should be less than non-null parameters. With N==0, it is the same as MaxIdx(...).
UrlEncode(String)
: Encodes a UTF-16 string using a non-standard, UTF-16-based percent-encoding.
UrlEncodeUTF8(String)
: Encodes a string using the RFC 3986-compliant* percent-encoding. The function converts characters into UTF8-based units and it skips characters defined in the unreserved character set. Unlike the above UrlEncode function, this function doesn't skip the common URL delimiters like "/", so it's not suitable for encoding full path URLs. You should use this function to encode specific URL components, like query component parameters.
A string function performs operations on text data. Use a string function to cleanse data, convert data to a different format or case, compute metrics about the data, or perform other manipulations. For parameters and examples, visit the Workflow String Functions article.
Contains(String, Target, CaseInsensitive=1)
: Searches for the occurrence of a particular string within a string. Returns True if (String) contains (Target), else returns False.
CountWords(string)
: Returns the count of words in the specified string. Words are defined by characters separated by a space.
DecomposeUnicodeForMatch(String)
: Removes accents and expands compound characters while converting to a narrow string. All accents and other decorations are removed.
EndsWith(String, Target, CaseInsensitive=1)
: Checks if a string ends with a particular string. Returns True if (String) ends with (Target), else returns False. It defaults to case insensitive.
FindNth(Initial String, Target, Instance)
: Finds the instance (nth occurrence) of a target string in the initial string and returns the 0-indexed position of the instance. The function is case-sensitive and doesn’t accept negative parameters.
FindString(String,Target)
: Searches for the occurrence of a particular string (Target) within another string (String) and returns the numeric position of its occurrence in the string. Returns the 0-based index of the first occurrence of (Target) in (String). Returns -1 if no occurrence.
GetLeft(String, Delimiter)
: Returns the left part of the provided (String) until the first instance of the specified 1 or more (Delimiter).
GetPart(String, Delimiter, Index)
: Uses the specified 1 or more (Delimiters) to divide (String) into substrings, and returns the substring in the position that is specified in (Index).
GetRight(String, Delimiter)
: Returns the right part of the provided (String) after the first instance of the specified 1 or more (Delimiters).
GetWord(string, n)
: Returns the Nth (0-based) word in the string. Words are defined as a collection of characters separated by a space. 0-based index, means the first word is at the 0 position.
Left(String, len)
: Returns the first (len) characters of the string (String). If len is less than 0 or greater than the length of String, String remains unchanged.
Length(String)
: Returns the length of the string (String).
LowerCase(String)
: Converts a string to lower case.
MD5_ASCII(String)
: Calculates the MD5 hash of the string. The string is expected to be only narrow characters. Wide characters are converted to '?' before computing the hash. Use for String types. You should avoid using this function on strings that might contain wide characters.
MD5_UNICODE(String)
: Calculates the MD5 hash of the string stored as UTF-16.
MD5_UTF8(String)
: Calculates the MD5 hash of the string stored as UTF-8.
PadLeft (String, len, char)
: Pads the string to the left with the specified character to the specified length. If the padding (char) is more than 1 character long, only the first character is used.
PadRight (String, len, char)
: Pads the string to the right with the specified character to the specified length. If the padding (char) is more than 1 character long, only the first character is used.
REGEX_CountMatches(String,pattern,icase)
: Returns the count of matches within the string to the pattern.
REGEX_Match(String,pattern,icase)
: Searches a string for an occurrence of a regular expression. Tells if the string matches the pattern from the first character to the end.
REGEX_Replace(String, pattern, replace, icase)
: Returns the string resulting from the RegEx find pattern and replace string.
Replace(String, Target, Replacement)
: Returns the string (String) after replacing each occurrence of the String (Target) with the String (Replacement).
ReplaceChar(String, y, z)
: Returns the string (String) after replacing each occurrence of the character (y) with the character (z). If the replacement character (z) is a string with more than 1 character, only the first character is used. If (z) is empty, each character (String) that matches any character in (y) is simply removed.
ReplaceFirst(String, Target, Replacement)
: Returns the string (String) after replacing the first occurrence of the string (Target) with the string (Replacement).
ReverseString(String)
: Reverses all the characters in the string.
Right(String, len)
: Returns the last (len) characters of the string. If len is less than 0 or greater than the length of String, the string remains unchanged.
StartsWith(String, Target, CaseInsensitive=1)
: Checks if a string starts with a particular string. Returns True if String starts with a particular string Target, else returns False.
STRCSPN(String, y)
: Returns the length of the initial segment of the string (String) consisting of charactersnotin the string (y).
StripQuotes(String)
: Removes a matched set of quotation marks or apostrophes from the ends of the string.
Substring(String, start, length)
: Returns the substring of (String) starting at (start), stopping after (length), if provided.
TitleCase(String)
: Converts a string to title case.
Trim(String, y)
: Removes the character(s) in the string (y) from the ends of the string (String). Y is optional and defaults to trimming white space. Notice in the TRIM function examples the specified characters are trimmed. It doesn't matter what order the characters are in.
TrimLeft(String, y)
: Removes character in the string (y) from the beginning of the string (String). Y is optional and defaults to trimming white space.
TrimRight(String, y)
: Removes character in the string (y) from the end of the string (String). Y is optional and defaults to trimming white space.
Uppercase(String)
: Converts a string to upper case.
UuidCreate()
: Creates a unique identifier.
A test function performs data comparisons. Use a test function to identify the data type of a value, or determine if a value exists. For parameters and examples, visit the Workflow Test Functions article.
CompareDictionary(a,b)
: Compares 2 strings ignoring case differences, and compares text that appears to consist of numbers, in numerical order. The function returns -1 if a < b, 0 if a==b, and 1 if a > b.
CompareDigits(a, b, nNumDigits)
: Compares 2 numbers and determines if they are the same to the given number of digits (nNumDigits).
CompareEpsilon(a, b, epsilon)
: Compares 2 floating-point numbers and returns True if they are within epsilon.
EqualStrings(a,b)
: Compares string (a) to string (b) to determine if they are an exact match. The function returns True if all characters in both a and b are identical and False if 1 or more characters are not identical.
IsEmpty(v)
: Tests if v is NULL or equal to "".
IsInteger(v)
: Tests if (v) contains a value that can be converted to an integer. If so, it returns True.
IsLowerCase(String)
: Returns True if all (1 or more) alphabetic characters in a string are lowercase and False if 1 or more alphabetic characters are uppercase. The function ignores non-alphabetic characters. Note that this function only applies to characters with a case distinction and might not apply to characters in all languages.
IsNull(v)
: Tests if (v) is NULL. Returns True if v is NULL, otherwise returns False.
IsNumber(v)
: Tests if the field type for (v) is a number or not.
IsString(v)
: Tests if the field type for (v) is a string or not.
IsUpperCase(String)
: Returns True if all (1 or more) alphabetic characters in a string are uppercase and False if 1 or more alphabetic characters are lowercase. The function ignores non-alphabetic characters. Note that this function only applies to characters with a case distinction and might not apply to characters in all languages.