A function is a subroutine that returns a single value. You can use functions to create calculations and conditions to filter data. Functions are similar to operators in that they manipulate data items and return a result. Functions differ from operators in the format in which they appear with their arguments. This format allows them to operate with zero, one, two, or more arguments:
function (argument, argument, ...)
Functions are of these general types:
Function | Description |
Accepts numeric input and returns a value that is a date. | |
Accepts numeric input and returns numeric values. | |
Executes a predefined function and returns an aggregate value. | |
Accepts character input and can return both character and number values. |
Date functions return a value that is either a date or a number that relates to a date.
Function | Description |
Returns a datetime value resulting from adding a number of days to a date. | |
Returns a datetime value resulting from adding a number of months to a date. | |
Returns a datetime value resulting from adding a number of years to a date. | |
Returns age as a day-month-year interval by subtracting a specified date from today's date. | |
Returns the number of days since Jan 1, 1900 inclusive. The value returned is negative if the date is before 1900. | |
Returns a numeric value for the day of the month from 1 to 31, from a date, datetime value, or interval. | |
Returns the number of days to the last day of the month from a date or datetime value. | |
Returns the first day of the month from a date or datetime value. | |
Returns the last day of the month from a date or datetime value. | |
Returns the month number as an integer from 1 to 12, from a date or datetime value. | |
Returns the number of months between two dates. If the first date is later than the second date, then the result is a negative number. | |
Returns the current date according to the date set on your computer. | |
Returns the year from the date. | |
Returns the number of years from one date to another date. |
Returns a datetime value resulting from adding a number of days to a date.
add-days (date_exp | datetime_exp, integer_exp)
add-days (today(), 10)
Returns the result: 03/30/2007 00:00
add-days (today(), -10)
Returns the result: 03/10/2007 00:00
Returns a datetime value resulting from adding a number of months to a date.
add-months (date_exp | datetime_exp, integer_exp)
add-months (today(), 10)
Returns the result: 01/20/2007 00:00
add-months (today(), -10)
Returns the result: 05/20/2007 00:00
Returns a datetime value resulting from adding a number of years to a date.
add-years (date_exp | datetime_exp, integer_exp)
add-years (today(), 10)
Returns the result: 03/20/2017 00:00
add-years (today(), -10)
Returns the result: 03/20/1997 00:00
Returns age as a month-day-year interval by subtracting a specified date from today's date.
age (date_exp | datetime_exp)
age (1996-08-19)
Returns the result: 02/01/0002 00:00
Returns the number of days since Jan. 1, 1900 inclusive.The value returned is negative if the date is before 1900.
date-to-days-from-1900 (date_exp | datetime_exp)
date-to-days-from-1900 (1998-03-20)
Returns the result: 35873
Returns a numeric value for the day of the month from 1 to 31, from a date, datetime value, or interval.
day (date_exp | datetime_exp | interval_exp)
day (2007-03-20)
Returns the result: 20
day (2007-03-20 18:22:00.000)
Returns the result: 20
day (20 00:00:00.000)
Returns the result: 20
Returns the number of days to the last day of the month from a date or datetime value.
days-to-end-of-month (date_exp | datetime_exp)
days-to-end-of-month (2007-03-20)
Returns the result: 11
Returns the first day of the month from a date or datetime value. The datetime value is converted from a date_exp value to a date with the same year and month, but the day is set to one.
first-of-month (date_exp | datetime_exp)
first-of-month (2007-03-20)
Returns the result: 03/01/2007 00:00
Returns the last day of the month from a date or datetime value.
last-of-month (date_exp | datetime_exp)
last-of-month (2007-03-21)
Returns the result: 03/31/2007 00:00
Returns the month number as an integer from 1 to 12, from a date or datetime value.
month (date_exp | datetime_exp)
month (2007-03-21)
Returns the result: 3
month (2007-03-21 09:21:00.000)
Returns the result: 3
Returns the number of months between two dates. If the first date is later than the second date, then the result is a negative number. This function does not round months; the days and time portions of the difference are ignored.
This function is processed only on an IQD data source.
months-between (date_exp_1 | datetime_exp_1, date_exp2 | datetime_exp2)
months-between (2007-03-21, add-months (2007-03-21, 4))
Returns the result: 4
months-between (2007-01-31, 2007-02-01)
Returns the result: 0
months-between (2007-01-31, 2007-03-21)
Returns the result: 1
Returns the current date according to the date set on your computer.
today ()
today ()
Returns today's date as the result.
Returns the year from the date.
year (date_exp | datetime_exp)
year (2007-12-10)
Returns the result: 2007
Returns the difference between the year values of date_exp1 and date_exp2. If date_exp1 is later than date_exp2, the result will be a negative number.
years-between (date_exp1 | datetime_exp1, date_exp2 | datetime_exp2)
years-between (2005-03-21, 2007-03-21)
Returns the result: 2
Accepts numeric input and returns numeric values.
Function | Description |
Returns a number rounded to the next highest integer. | |
Returns a number rounded to the next lowest integer. | |
Returns the integer obtained from truncating the result of an integer divided by a second integer. | |
Returns the remainder (modulus) of an integer divided by a second integer. | |
Returns a string from a number. | |
Returns a number rounded down. | |
Returns a number rounded to the nearest value. | |
Returns a number rounded up. | |
Returns a number rounded toward zero. | |
Returns the square root of a positive number. |
Returns a number rounded to the next highest integer.
ceiling (numeric_exp)
ceiling (-1.23)
Returns the result: -1
ceiling (1.23)
Returns the result: 2
Returns a number rounded to the next lowest integer.
floor (numeric_exp)
floor (-1.23)
Returns the result: -2
floor (3.45)
Returns the result: 3
Returns the integer obtained from truncating the result of an integer divided by a second integer.
integer-divide (integer_exp1, integer_exp2)
integer-divide (10, 20)
Returns the result: 0
integer-divide (20, 6)
Returns the result: 3
Returns the remainder (modulus) of an integer divided by a second integer. If the second integer is zero, Transformer issues a divide by zero error.
mod (integer_exp1, integer_exp2)
mod (245,3)
Returns the result: 2
Returns a string from a number. If the number is negative, a minus sign (-) precedes the string. If the number is a real number, only the truncated integer part of the number is converted to a string.
number-to-string (numeric_exp)
number-to-string (12345)
Returns the result: 12345
number-to-string (12345.678)
Returns the result: 12345
Returns a number rounded down.
The integer_exp value determines the position that is rounded. A positive integer_exp acts on the digits to the right of the decimal point. A negative integer_exp acts on the digits to the left of the decimal point. An integer_exp value of zero rounds the number and removes the decimal places.
round-down (numeric_exp, integer_exp)
round-down (-113.6667, 0)
Returns the result: -114
round-down (-113.6667, 1)
Returns the result: -113.7
round-down (-113.6667, -1)
Returns the result: -120
round-down (-113.6667, -2)
Returns the result: -200
round-down (366.2162, 0)
Returns the result: 366
round-down (366.2162, 1)
Returns the result: 366.2
round-down (366.2162, -1)
Returns the result: 360
round-down (366.2162, -2)
Returns the result: 300
Returns a number rounded to the nearest value.
The integer_exp value determines the position that is rounded. A positive integer_exp value acts on the digits to the right of the decimal point. A negative integer_exp value acts on the digits to the left of the decimal point. An integer_exp value of zero rounds the number and removes the decimal places.
round-near (numeric_exp, integer_exp)
round-near (-113.6667, 0)
Returns the result: -114
round-near (-113.6667, 1)
Returns the result: -113.7
round-near (-113.6667, -1)
Returns the result: -110
round-near (-113.6667, -2)
Returns the result: -100
round-near (366.2162, 0)
Returns the result: 366
round-near (366.2162, 1)
Returns the result: 366.2
round-near (366.2162, -1)
Returns the result: 370
round-near (366.2162, -2)
Returns the result: 400
Returns a number rounded up.
The An integer_exp value determines the position that is rounded. A positive An integer_exp value acts on the digits to the right of the decimal point. A negative An integer_exp value acts on the digits to the left of the decimal point. An integer_exp value of zero rounds the number and removes the decimal places.
round-up (numeric_exp, integer_exp)
round-up (-113.6667, 0)
Returns the result: -113
round-up (-113.6667, 1)
Returns the result: -113.6
round-up (-113.6667, -1)
Returns the result: -110
round-up (-113.6667, -2)
Returns the result: -100
round-up (366.2162, 0)
Returns the result: 367
round-up (366.2162, 1)
Returns the result: 366.3
round-up (366.2162, -1)
Returns the result: 370
round-up (366.2162, -2)
Returns the result: 400
Returns a number rounded toward zero.
The integer_exp value determines the position that is rounded. A positive integer_exp value acts on the digits to the right of the decimal point. A negative integer_exp value acts on the digits to the left of the decimal point. An integer_exp value of zero rounds the number and removes the decimal places.
round-zero (numeric_exp, integer_exp)
round-zero (-113.6667, 0)
Returns the result: -113
round-zero (-113.6667, 1)
Returns the result: -113.6
round-zero (-113.6667, -1)
Returns the result: -110
round-zero (-113.6667, -2)
Returns the result: -100
round-zero (366.2162, 0)
Returns the result: 366
round-zero (366.2162, 1)
Returns the result: 366.2
round-zero (366.2162, -1)
Returns the result: 360
round-zero (366.2162, -2)
Returns the result: 300
Returns the square root of a positive number.
sqrt (numeric_exp)
sqrt (2)
Returns the result: 1.4142135623731
sqrt (64)
Returns the result: 8
Text functions accept character input and can return both character and numeric values.
Function | Description |
Returns the number of characters in a string. | |
Returns the first word in a string. | |
Returns a specific number of characters, starting at the left of the string. | |
Converts uppercase characters to lowercase. | |
Returns the starting position of a string in a second string. | |
Reverses the characters in a string. | |
Returns a specific number of characters, starting at the right of the string. | |
| String-to-Integer | Converts a string to an integer. |
Returns a substring from a string. | |
Returns a string with leading spaces removed. | |
Returns a string with trailing spaces removed. | |
Converts lowercase characters to uppercase. |
Note: The Pack, Spread and Substitute text functions are not supported in Transformer version 8.x.
Returns the number of characters in a string.
char_length (string_exp)
char_length ('ABCDEFG')Returns the result: 7
char_length ('')Returns the result: 0
char_length (' ')Returns the result: 1
Returns the first word in a string.
first-word (string_exp)
first-word ('Cat sat on the mat')Returns the result: Cat
Returns a specific number of characters, starting at the left of the string.
left (string_exp, integer_exp)
left ('ABCDEFG', 2)Returns the result: AB
Converts uppercase characters to lowercase.
lower (string_exp)
lower ('ABCDEFG')Returns the result: abcdefg
Returns the starting position of string_exp1 in string_exp2. The first character in a string is at position one.
position (string_exp1, string_exp2)
position ('DEF', 'ABCDEF')Returns the result: 4
position ('Z', 'ABCDEFGH'Returns the result: 0
Reverses the characters in a string.
reverse (string_exp)
reverse ('ABCDEF')Returns the result: FEDCBA
Returns a specific number of characters, starting at the right of the string.
right (string_exp, integer_exp)
right ('ABCDEFG', 3)Returns the result: EFG
Returns the integer representation of string_exp.
string-to-integer (string_exp)
string-to-integer (' 101 tents')Returns the result: 101
Returns a substring from a string. The first character in the string is at position one.
substring (string_exp, integer_exp1, integer_exp2)
where:
string_exp is the string from which you want to extract a substring
integer_exp1 is the position of the first character in the substring
integer_exp2 is the desired length of the substring
substring ('abdefg', 3, 2)Returns the result: de
Returns a string with leading spaces removed. For example, if you merge two data items with leading spaces, use trim-leading to eliminate the spaces between them.
trim-leading (string_exp)
trim-leading (' ABC')Returns the result: ABC
Returns a string with trailing spaces removed. For example, if you merge two data items in an expression and the data items have trailing spaces, the spaces between the data items can be eliminated using the trim-trailing function.
trim-trailing (string_exp)
trim-trailing ('XYZ ')Returns the result: XYZ
Converts lowercase characters to uppercase.
upper (string_exp)
upper ('Auriga')Returns the result: AURIGA
upper ('DeEr')Returns the result: DEER