You can use several functions in the Taskflow Expression Editor.
Some of the key functions are described below:
Asset detail functions
You can use the following asset detail functions from the Miscellaneous section of the Expression Editor:
- getAssetLocation
- getAssetName
- getInstanceStartTime
Character functions
You can use the following character functions from the Strings section of the Expression Editor:
- instr
- lpad
- ltrim
- rtrim
Conversion functions
You can use the following conversion functions from the Dates and Times, Miscellaneous, or String sections of the Expression Editor:
- toChar(Numbers)
- toDate
- toDecimal
- toInteger
Data cleansing functions
You can use the following data cleansing function from the Miscellaneous section of the Expression Editor:
- in
Date functions
You can use the following date functions from the Dates and Times section of the Expression Editor:
- addToDate
- dateDiff
- getDatePart
- lastDay
- trunc
Numeric functions
You can use the following numeric function from the Numbers section of the Expression Editor:
- round(Numbers)
- trunc
Organization detail functions
You can use the following organization detail functions from the Miscellaneous section of the Expression Editor:
- getDefaultFailureEmailNotification
- getDefaultSuccessEmailNotification
- getDefaultWarningEmailNotification
- getOrganizationName
Test functions
You can use the following test functions from the Miscellaneous section of the Expression Editor:
- decode
- iif
- isNull
When you use an integer type variable in a function, explicit casting of number is required for best results. For example, rewrite $output.current to number($output.current) in functions where you pass an integer type variable.
addToDate
Adds a specified amount to one part of a datetime value, and returns a date in the same format as the date you pass to the function. The addToDate function accepts positive and negative integer values. Use addToDate to change the following parts of a date:
•Year. Enter a positive or negative integer in the amount argument. Use any of the year format strings: Y, YY, YYY, or YYYY. The following expression adds 10 years to all dates in the SHIP_DATE column:
•Month. Enter a positive or negative integer in the amount argument. Use any of the month format strings: MM, MON, MONTH. The following expression subtracts 10 months from each date in the SHIP_DATE column:
•Day. Enter a positive or negative integer in the amount argument. Use any of the day format strings: D, DD, DDD, DY, and DAY. The following expression adds 10 days to each date in the SHIP_DATE column:
•Hour. Enter a positive or negative integer in the amount argument. Use any of the hour format strings: HH, HH12, HH24. The following expression adds 14 hours to each date in the SHIP_DATE column:
•Minute. Enter a positive or negative integer in the amount argument. Use the MI format string to set the minute. The following expression adds 25 minutes to each date in the SHIP_DATE column:
•Seconds. Enter a positive or negative integer in the amount argument. Use the SS format string to set the second. The following expression adds 59 seconds to each date in the SHIP_DATE column:
•Milliseconds. Enter a positive or negative integer in the amount argument. Use the MS format string to set the milliseconds. The following expression adds 125 milliseconds to each date in the SHIP_DATE column:
•Microseconds. Enter a positive or negative integer in the amount argument. Use the US format string to set the microseconds. The following expression adds 2,000 microseconds to each date in the SHIP_DATE column:
Note: You must manually add the xs:dateTime phrase and enclose the date values within single quotation marks.
The following table describes the arguments:
Argument
Required/ Optional
Description
date
Required
Date/Time data type. Passes the values that you want to change.
You can enter any valid transformation expression.
format
Required
A format string that specifies the portion of the date value that you want to change. Enclose the format string within single quotation marks, for example, 'mm'. The format string is not case sensitive.
amount
Required
An integer value that specifies the amount of years, months, days, hours, and so on by which you want to change the date value. You can enter any valid transformation expression that evaluates to an integer.
Return Value
Date in the same format as the date you pass to this function.
NULL if a null value is passed as an argument to the function.
Examples
The following expressions all add one month to each date in the DATE_SHIPPED column. If you pass a value that creates a day that does not exist in a particular month, addToDate returns the last day of the month. For example, if you add one month to Jan 31 1998, addToDate returns Feb 28 1998.
Also, addToDate recognizes leap years and adds one month to Jan 29 2000:
The following table shows some sample values and return values:
DATE_SHIPPED
RETURN VALUE
Jan 1 1997 12:00:30AM
Dec 31 1996 9:00:30AM
Jan 31 1997 6:24:45PM
Jan 31 1997 3:24:45AM
Oct 9 1997 2:30:12PM
Oct 8 1997 11:30:12PM
Mar 3 1996 5:12:20AM
Mar 2 1996 2:12:20PM
Mar 1 1996 5:32:12AM
Feb 29 1996 2:32:12PM (Leap Year)
NULL
NULL
base64Decode
Returns the base64-decoded version of the input string provided based on the character set specified in the charSet argument. This function is typically used for attachments.
Syntax
util:base64Decode(data, charSet)
The following table describes the arguments:
Argument
Required/
Optional
Description
data
Required
String data type. Data that you want to decode.
charSet
Optional
Character decoding of the data. Taskflows support the character sets that Azul JDK supports for encoding. For example, US-ASCII, ISO-8859-1, UTF-8, UTF-16BE, UTF-16LE, and UTF-16.
Default is UTF-8.
Return Value
Decoded value.
NULL if the input is a null value.
Example
You encoded MQSeries message IDs and wrote them to a flat file. You want to read data from the flat file source, including the MQSeries message IDs. You can use base64Decode to decode the IDs and convert them to their original string value.
dateDiff
Returns the length of time between two dates. You can specify the format as years, months, days, hours, minutes, seconds, milliseconds, or microseconds. The dateDiff function subtracts the second date from the first date and returns the difference.
The dateDiff function calculates the value based on the number of months instead of the number of days. It calculates the date differences for partial months with the days selected in each month. To calculate the date difference for the partial month, dateDiff adds the days used within the month. It then divides the value with the total number of days in the selected month.
The dateDiff function gives a different value for the same period in the leap year period and a non-leap year period. The difference occurs when February is part of the dateDiff function. The dateDiff function divides the days with 29 for February for a leap year and 28 if it is not a leap year.
For example, you want to calculate the number of months from September 13 to February 19. In a leap year period, dateDiff calculates the month of February as 19/29 months or 0.655 months. In a non-leap year period, dateDiff calculates the month of February as 19/28 months or 0.678 months. The dateDiff function similarly calculates the difference in the dates for the remaining months and the dateDiff function returns the total value for the specified period.
Note: Some databases might use a different algorithm to calculate the difference in dates.
Note: You must manually add the xs:dateTime phrase and enclose the date values within single quotation marks.
The following table describes the arguments:
Argument
Required/ Optional
Description
date
Required
Date/Time data type. Passes the values for the first date that you want to compare.
You can enter any valid transformation expression.
date
Required
Date/Time data type. Passes the values for the second date that you want to compare.
You can enter any valid transformation expression.
format
Required
Format string that specifies the date or time measurement. You can specify years, months, days, hours, minutes, seconds, milliseconds, or microseconds. You can specify only one part of the date, such as 'mm'. Enclose the format strings within single quotation marks. The format string is not case sensitive. For example, the format string 'mm' is the same as 'MM', 'Mm', or 'mM'.
Return Value
Double value. If the first date is later than the second date, the return value is a positive number. If the first date is earlier than the second date, the return value is a negative number.
0 if the dates are the same.
NULL if one (or both) of the date values is NULL.
Examples
The following expressions return the number of hours between the DATE_PROMISED and DATE_SHIPPED columns:
The following table lists some sample values and return values:
DATE_PROMISED
DATE_SHIPPED
LEAP YEAR VALUE (in Months)
NON-LEAP YEAR VALUE (in Months)
Sept 13
Feb 19
-5.237931034
-5.260714286
NULL
Feb 19
NULL
N/A
Sept 13
NULL
NULL
N/A
decode
Searches a column for a value that you specify. If the function finds the value, it returns a result value, which you define. You can build an unlimited number of searches within a decode function.
If you use decode to search for a value in a string column, you can either trim trailing blank characters with the rtrim function or include the blanks in the search string.
You can enter any valid transformation expression. You can pass any data type except Binary.
To pass a NULL value, you must specify an empty sequence in the following format: ()
search1
Required
Passes the values for which you want to search.
You can enter any valid transformation expression. You can pass any value with the same data type as the value argument. The search value must match the value argument. You cannot search for a portion of a value. Also, the search value is case sensitive.
For example, if you want to search for the string 'Halogen Flashlight' in a particular column, you must enter 'Halogen Flashlight, not just 'Halogen'. If you enter 'Halogen', the search does not find a matching value.
To pass a NULL value, you must specify an empty sequence in the following format: ()
result1
Required
The value that you want to return if the search finds a matching value.
You can enter any valid transformation expression and pass any data type except Binary.
To pass a NULL value, you must specify an empty sequence in the following format: ()
args
Required
Pairs of search values and result values separated by a comma.
The return values in this expression are 5, 1.414213562, and 3.141592654. The first result is an integer, and the other results are decimal. The decimal data type has a greater precision than the integer data type. This expression always writes the result as a decimal value.
You cannot create a decode function with both string and numeric return values.
For example, the following expression is not valid:
The following table lists some sample values and return values:
ITEM_ID
RETURN VALUE
10
Flashlight
14
Regulator
17
NONE
20
Knife
25
NONE
NULL
NONE
40
Tank
The decode function returns the default value of NONE for items 17 and 25 because the search values did not match the ITEM_ID. Also, decode returns NONE for the NULL ITEM_ID.
The following expression tests multiple columns and conditions, evaluated in a top to bottom order for TRUE or FALSE:
util:decode( TRUE, Var1 = 22, 'Variable 1 was 22!', Var2 = 49, 'Variable 2 was 49!', Var1 < 23, 'Variable 1 was less than 23.', Var2 > 30, 'Variable 2 was more than 30.', 'Variables were out of desired ranges.')
The following table lists some sample values and return values:
Var1
Var2
RETURN VALUE
21
47
Variable 1 was less than 23.
22
49
Variable 1 was 22!
23
49
Variable 2 was 49!
24
27
Variables were out of desired ranges.
25
50
Variable 2 was more than 30.
getAssetLocation
Returns the location where the taskflow that uses the function is stored.
For example, you can use the function to find the taskflow location for debugging purposes.
Syntax
util:getAssetLocation()
The getAssetLocation function does not use an argument.
Return Value
Location where the taskflow that uses the function is stored.
Example
If you use the getAssetLocation function in a taskflow that is stored under Default\Orders, the function returns the following value:
Default\Orders
getAssetName
Returns the name of the taskflow that uses the function.
For example, you can use the function in a Notification Task step to include the taskflow name in a taskflow failure email notification that you send to stakeholders.
Syntax
util:getAssetName()
The getAssetName function does not use an argument.
Return Value
Name of the taskflow that uses the function.
Example
If you use the getAssetName function in a taskflow that is named Order Management, the function returns the following value:
Order Management
getDatePart
Returns the specified part of a date as an integer value. Therefore, if you create an expression that returns the month portion of the date, and pass a date such as Apr 1 1997 00:00:00, getDatePart returns 4.
Syntax
date:getDatePart(xs:dateTime('date'), 'format')
Note: You must manually add the xs:dateTime phrase and enclose the date values within single quotation marks.
The following table describes the arguments:
Argument
Required/ Optional
Description
date
Required
Date/Time data type.
You can enter any valid transformation expression.
format
Required
A format string that specifies the portion of the date value that you want to return. Enclose format strings within single quotation marks, for example, 'mm'. The format string is not case sensitive.
For example, if you pass the date Apr 1 1997 to getDatePart, the format strings 'Y', 'YY', 'YYY', or 'YYYY' all return 1997.
Return Value
Integer representing the specified part of the date.
NULL if a value passed to the function is NULL.
Examples
The following expressions return the hour for each date in the DATE_SHIPPED column. 12:00:00AM returns 0 because the default date format is based on the 24 hour interval:
The following table lists some sample values and return values:
DATE_SHIPPED
RETURN VALUE
Mar 13 1997 12:00:00AM
1997
June 3 1997 11:30:44PM
1997
NULL
NULL
getDefaultFailureEmailNotification
Returns the default email addresses configured in the Failure Email Notifications field for the organization in Administrator. In addition, when you use this function in the Email To, Email Cc, or Email Bcc fields in the Notification Task step, it also sends the notification to the email addresses.
Syntax
util:getDefaultFailureEmailNotification()
The getDefaultFailureEmailNotification function does not use an argument.
Return Value
Email addresses configured for failure notifications for the organization in Administrator. If the function is used in the Email To, Email Cc, or Email Bcc fields in the Notification Task step, the notification is sent to the returned email addresses.
Example
If you use the getDefaultFailureEmailNotification function in a taskflow and the Failure Email Notifications field contains the value abc@informatica.com, the function returns the following value:
abc@informatica.com
If the function is used in the Email To, Email Cc, or Email Bcc fields in the Notification Task step, it also sends the notification to abc@informatica.com.
getDefaultSuccessEmailNotification
Returns the default email addresses configured in the Success Email Notifications field for the organization in Administrator. In addition, when you use this function in the Email To, Email Cc, or Email Bcc fields in the Notification Task step, it also sends the notification to the email addresses.
Syntax
util:getDefaultSuccessEmailNotification()
The getDefaultSuccessEmailNotification function does not use an argument.
Return Value
Email addresses configured for success notifications for the organization in Administrator. If the function is used in the Email To, Email Cc, or Email Bcc fields in the Notification Task step, the notification is sent to the returned email address.
Example
If you use the getDefaultSuccessEmailNotification function in a taskflow and the Success Email Notifications field contains the value abc@informatica.com, the function returns the following value:
abc@informatica.com
If the function is used in the Email To, Email Cc, or Email Bcc fields in the Notification Task step, it also sends the notification to abc@informatica.com.
getDefaultWarningEmailNotification
Returns the default email addresses configured in the Warning Email Notifications field for the organization in Administrator. In addition, when you use this function in the Email To, Email Cc, or Email Bcc fields in the Notification Task step, it also sends the notification to the email addresses.
Syntax
util:getDefaultWarningEmailNotification()
The getDefaultWarningEmailNotification function does not use an argument.
Return Value
Email addresses configured for warning notifications for the organization in Administrator. If the function is used in the Email To, Email Cc, or Email Bcc fields in the Notification Task step, the notification is sent to the returned email address.
Example
If you use the getDefaultWarningEmailNotification function in a taskflow and the Warning Email Notifications field contains the value abc@informatica.com, the function returns the following value:
abc@informatica.com
If the function is used in the Email To, Email Cc, or Email Bcc fields in the Notification Task step, it also sends the notification to abc@informatica.com.
getInstanceStartTime
Returns the start date and start time of the running instance of the taskflow that uses the function.
For example, you can use the function to find when a taskflow started running and abort the taskflow if it has been running beyond the expected duration.
Syntax
util:getInstanceStartTime()
The getInstanceStartTime function does not use an argument.
Return Value
Start date and start time of the running instance of the taskflow that uses the function.
The return value is in the Coordinated Universal Time (UTC) format as follows:
YYY-MM-DDTHH:mm:ss.sssZ
Example
If you use the getInstanceStartTime function in a taskflow that was started on January 19, 2021, the function returns the following value:
2021-01-19T10:11:21.047Z
getOrganizationName
Returns the Informatica Intelligent Cloud Services organization name in the context of the executing instance.
Syntax
util:getOrganizationName()
Return Value
When the taskflow is deployed on the cloud server, the function returns the Informatica Intelligent Cloud Services organization name as the output.
iif
Returns one of two values that you specify based on the results of a condition.
Syntax
util:iif(condition, val1 ,val2)
The following table describes the arguments:
Argument
Required/Optional
Description
condition
Required
The condition that you want to evaluate.
You can enter any valid transformation expression that evaluates to TRUE or FALSE.
val1
Required
The value that you want to return if the condition is TRUE. The return value is always the data type specified by this argument.
You can enter any valid transformation expression, including another iif expression. You can pass any data type except Binary.
val2
Optional
The value that you want to return if the condition is FALSE.
You can enter any valid transformation expression, including another iif expression. You can pass any data type except Binary.
The FALSE (val2) condition in the iif function is not required. If you omit val2, the function returns one of the following values when the condition is FALSE:
•0 if val1 is a Numeric data type.
•Empty string if val1 is a String data type.
•NULL if val1 is a Date/Time data type.
For example, the following expression does not include a FALSE condition and val1 is a string data type so decode returns an empty string for each row that evaluates to FALSE:
util:iif(SALES > 100, EMP_NAME)
The following table lists some sample values and return values:
SALES
EMP_NAME
RETURN VALUE
150
John Smith
John Smith
50
Pierre Bleu
'' (empty string)
120
Sally Green
Sally Green
NULL
Greg Jones
'' (empty string)
Return Value
val1 if the condition is TRUE.
val2 if the condition is FALSE.
For example, the following expression includes the FALSE condition NULL so decode returns NULL for each row that evaluates to FALSE:
util:iif(SALES > 100, EMP_NAME, NULL)
The following table lists some sample values and return values:
SALES
EMP_NAME
RETURN VALUE
150
John Smith
John Smith
50
Pierre Bleu
NULL
120
Sally Green
Sally Green
NULL
Greg Jones
NULL
iif and data types
When you use iif, the data type of the return value is the same as the data type of the result with the greatest precision.
For example, you have the following expression:
util:iif(SALES < 100, 1, .3333)
The TRUE result (1) is an integer and the FALSE result (.3333) is a decimal. The Decimal data type has a greater precision than the Integer data type. Therefore, the data type of the return value is always a decimal value.
Special uses of iif
Use nested iif statements to test multiple conditions. The following example tests for various conditions and returns 0 if sales is 0 or negative:
Matches input data to a list of values. By default, the match is case sensitive.
Syntax
util:in(valueToSearch, values, caseFlag)
The following table describes the arguments:
Argument
Required/
Optional
Description
valueToSearch
Required
Can be a string, date, or numeric value. Input value that you want to match against a comma-separated list of values.
To pass a NULL value, you must specify an empty sequence in the following format: ()
values
Required
Can be a string, date, or numeric value. Comma-separated list of values that you want to search for. Values can be columns. There is no limit to the maximum number of values that you can list.
To pass a NULL value, you must specify an empty sequence in the following format: ()
caseFlag
Optional
Must be an integer. Determines whether the arguments in this function are case sensitive. You can enter any valid transformation expression.
When caseFlag is a number other than 0, the function is case sensitive.
When caseFlag is a null value or 0, the function is not case sensitive.
Return Value
TRUE (1) if the input value matches the list of values.
FALSE (0) if the input value does not match the list of values.
NULL if the input is a null value.
Example
The following expression determines if the input value is a safety knife, chisel point knife, or medium titanium knife. The input values do not have to match the case of the values in the comma-separated list:
util:in(ITEM_NAME, ‘Chisel Point Knife’, ‘Medium Titanium Knife’, ‘Safety Knife’, 0)
The following table lists some sample values and return values:
ITEM_NAME
RETURN VALUE
Stabilizing Vest
0 (FALSE)
Safety knife
1 (TRUE)
Medium Titanium knife
1 (TRUE)
NULL
instr
Returns the position of a character set in a string, counting from left to right.
The string must be a character string. You can enter any valid transformation expression.
The results of the expression must be a character string. Otherwise, instr converts the value to a string before evaluating it.
search
Required
The set of characters that you want to search for.
You can enter any valid transformation expression. If you want to search for a character string, enclose the characters that you want to search for within single or double quotation marks.
The value must match a part of the string. For example, if you use instr('Alfred Pope', 'Alfred Smith') the function returns 0.
The value is case sensitive.
start
Optional
The position in the string where you want to start the search. You can enter any valid transformation expression. The value must be an integer.
The default is 1, meaning that instr starts the search at the first character in the string.
If the start position is 0, instr searches from the first character in the string. If the start position is a positive number, instr locates the start position by counting from the beginning of the string. If the start position is a negative number, instr locates the start position by counting from the end of the string. If you omit this argument, the function uses the default value of 1.
occurrence
Optional
You can enter any valid transformation expression. If the search value appears more than once in the string, you can specify which occurrence you want to search for. For example, you would enter 2 to search for the second occurrence from the start position.
You can enter a positive integer that is greater than 0.
If you omit this argument, the function uses the default value of 1, which means that instr searches for the first occurrence of the search value. If you pass a decimal value, the function rounds it to the nearest integer value. If you pass a negative integer or 0, the function is not valid.
comparison_type
Optional
The string comparison type, either linguistic or binary.
Linguistic comparisons take language-specific collation rules into account, while binary comparisons perform bitwise matching. For example, the German sharp s character matches the string “ss” in a linguistic comparison, but not in a binary comparison. Binary comparisons run faster than linguistic comparisons.
You must enter one of the following integer values:
- 0: instr performs a linguistic string comparison.
- 1: instr performs a binary string comparison.
Default is 0.
Return Value
Integer if the search is successful. Integer represents the position of the first character in the search argument, counting from left to right.
0 if the search is unsuccessful.
NULL if a value passed to instr is NULL.
Examples
The following expression returns the position of the first occurrence of the letter ‘a’, starting from the beginning of each company name:
sff:instr( COMPANY, 'a' )
The following table lists some sample values and return values:
COMPANY
RETURN VALUE
Blue Fin Aqua Center
13
Maco Shark Shop
2
Scuba Gear
5
Frank's Dive Shop
3
VIP Diving Club
0
Because the search argument is case sensitive, it skips the ‘A’ in ‘Blue Fin Aqua Center’, and returns the position for the ‘a’ in ‘Aqua’.
The following expression returns the position of the second occurrence of the letter ‘a’, starting from the beginning of each company name:
sff:instr( COMPANY, 'a', 1, 2 )
The following table lists some sample values and return values:
COMPANY
RETURN VALUE
Blue Fin Aqua Center
0
Maco Shark Shop
8
Scuba Gear
9
Frank's Dive Shop
0
VIP Diving Club
0
Because the search argument is case sensitive, it skips the ‘A’ in ‘Blue Fin Aqua Center’, and returns 0.
The following expression returns the position of the second occurrence of the letter ‘a’ in each company name, starting from the last character in the company name.
sff:instr( COMPANY, 'a', -1, 2 )
The following table lists some sample values and return values:
COMPANY
RETURN VALUE
Blue Fin Aqua Center
0
Maco Shark Shop
2
Scuba Gear
5
Frank's Dive Shop
0
VIP Diving Club
0
Because the search argument is case sensitive, it skips the ‘A’ in 'Blue Fin Aqua Center’, and returns 0.
The following expression returns the position of the first character in the string ‘Blue Fin Aqua Center’, starting from the last character in the company name:
sff:instr( COMPANY, 'Blue Fin Aqua Center', -1, 1 )
The following table lists some sample values and return values:
COMPANY
RETURN VALUE
Blue Fin Aqua Center
1
Maco Shark Shop
0
Scuba Gear
0
Frank's Dive Shop
0
VIP Diving Club
0
isNull
Returns whether a value is NULL.
Syntax
util:isNull(value)
The following table describes the argument for this command:
Argument
Required/
Optional
Description
value
Required
Passes the rows that you want to evaluate. You can enter any valid transformation expression. You can pass a value of any data type except Binary.
Return Value
TRUE (1) if the value is NULL.
FALSE (0) if the value is not NULL.
Example
The following example checks for NULL values in the items table:
util:isNull( ITEM_NAME )
The following table lists some sample values and return values:
ITEM_NAME
RETURN VALUE
Flashlight
0 (FALSE)
NULL
1 (TRUE)
Regulator system
0 (FALSE)
''
0 (FALSE) Empty string is not NULL
lastDay
Returns the date of the last day of the month for each date in a column.
Syntax
date:lastDay(date)
The following table describes the argument for this command:
Argument
Required/
Optional
Description
date
Required
Date/Time data type. Passes the date for which you want to return the last day of the month.
You can enter any valid transformation expression that evaluates to a date.
Return Value
Date. The last day of the month for the date value that you pass to this function.
NULL if a value in the selected column is NULL.
Example
The following expression returns the current date as the last day:
date:lastDay(fn:current-dateTime('DATE'))
The following table lists some sample values and return values:
DATE
RETURN VALUE
18-04-98 01:00
Apr 18 1998 01:00:00 AM
20-08-99 05:00
Aug 20 1999 05:00:00 AM
The following expression returns the last day of the previous month for each date in the DATE column:
The following table lists some sample values and return values:
DATE
RETURN VALUE
Apr 1 1998 12:00:00AM
Mar 31 1998 12:00:00AM
Jan 6 1998 12:00:00AM
Dec 31 1997 12:00:00AM
Feb 2 1996 12:00:00AM
Jan 31 1996 12:00:00AM
NULL
NULL
You can nest toDate to convert string values to a date. toDate function always includes time information. If you pass a string that does not have a time value, the date returned will include the time 00:00:00.
The following example returns the last day of the month for each date in the same format as the string:
date:lastDay(toDate('DATE', 'MON-DD-YYYY'))
The following table lists some sample values and return values:
DATE
RETURN VALUE
'18-NOV-98'
Nov-30-1998 00:00:00
'28-APR-98'
Apr-30-1998 00:00:00
NULL
NULL
'18-FEB-96'
Feb-29-1996 00:00:00(Leap year)
date:lastDay(date:toDate("DATE", "YYYY-MM-DD"))
The following table lists some sample values and return values:
DATE
RETURN VALUE
'18-NOV-98'
1998-Nov-30 00:00:00
'28-APR-98'
1998-Apr-30 00:00:00
NULL
NULL
'18-FEB-96'
1996-Feb-29 00:00:00(Leap year)
lpad
Adds a set of blank characters to the beginning of a string to set the string to a specified length.
Syntax
sff:lpad(first_string, length, second_string)
The following table describes the arguments:
Argument
Required/
Optional
Description
first_string
Required
Can be a character string. Passes the string that you want to change. You can enter any valid transformation expression.
To pass a NULL value, you must specify an empty sequence in the following format: ()
length
Required
Must be a positive integer literal. This argument specifies the length that you want for each string. When length is a negative number, lpad returns NULL.
second_string
Optional
Can be any string value. The characters that you want to append to the left-side of the first_string values. You can enter any valid transformation expression. You can enter a specific string literal. However, enclose the characters you want to add to the beginning of the string within single quotation marks, as in 'abc'. This argument is case sensitive. If you omit the second_string, the function pads the beginning of the first string with blank characters.
To pass a NULL value, you must specify an empty sequence in the following format: ()
Return Value
String of the specified length.
NULL if a value passed to the function is NULL or if length is a negative number.
Example
The following expression standardizes numbers to six digits by padding them with leading zeros.
sff:lpad(PART_NUM, 6, '0')
The following table lists some sample values and return values:
PART_NUM
RETURN VALUE
702
000702
1
000001
0553
000553
484834
484834
lpad counts the length from left to right. If the first string is longer than the length, lpad truncates the string from right to left. For example, lpad(‘alphabetical’, 5, ‘x’) returns the string ‘alpha’.
If the second string is longer than the total characters needed to return the specified length, lpad uses a portion of the second string:
sff:lpad(ITEM_NAME, 16, '*..*')
The following table lists some sample values and return values:
ITEM_NAME
RETURN VALUE
Flashlight
*..**.Flashlight
Compass
*..**..**Compass
Regulator System
Regulator System
Safety Knife
*..*Safety Knife
The following expression shows how lpad handles negative values for the length argument for each row in the ITEM_NAME column:
sff:lpad(ITEM_NAME, -5, '.')
The following table lists some sample values and return values:
ITEM_NAME
RETURN VALUE
Flashlight
NULL
Compass
NULL
Regulator System
NULL
ltrim
Removes leading spaces or characters from the beginning of a string.
If you do not specify the trim_set argument in the expression, ltrim removes both single-byte and double-byte spaces from the beginning of a string.
If you use ltrim to remove characters from a string, ltrim compares the trim_set to each character in the str argument, character-by-character, starting from the left side of the string. If the character in the string matches any character in the trim_set, ltrim removes it. The ltrim function continues comparing and removing characters until it fails to find a matching character in the trim_set. Then it returns the string, which does not include matching characters.
Syntax
sff:ltrim(str, trim_set)
The following table describes the arguments:
Arguments
Required/
Optional
Description
str
Required
Any string value. Passes the strings that you want to modify. You can enter any valid transformation expression. Use operators to perform comparisons or concatenate strings before removing characters from the beginning of a string.
You must enclose the string value within single or double quotation marks.
To pass a NULL value, you must specify an empty sequence in the following format: ()
trim_set
Optional
Any string value. Passes the characters that you want to remove from the beginning of the first string. You can enter any valid transformation expression. You can also enter a character string.
You must enclose the trim set value within single or double quotation marks.
To pass a NULL value, you must specify an empty sequence in the following format: ()
The ltrim function is case sensitive. For example, if you want to remove the 'A' character from the string 'Alfredo', you would enter 'A', not 'a'.
Return Value
String. The string values with the specified characters in the trim_set argument removed.
NULL if a value passed to ltrim is NULL. If the trim_set is NULL, ltrim returns NULL.
Example
The following expression removes the characters ‘S’ and ‘.’ from the strings in the LAST_NAME column:
sff:ltrim( LAST_NAME, 'S.')
The following table lists some sample values and return values:
LAST_NAME
RETURN VALUE
Nelson
Nelson
Osborne
Osborne
NULL
NULL
S. MacDonald
MacDonald
Sawyer
awyer
H. Bender
H. Bender
Steadman
teadman
The ltrim function removes ‘S.’ from S. MacDonald and the ‘S’ from both Sawyer and Steadman, but not the period from H. Bender. This is because ltrim searches, character-by-character, for the set of characters you specify in the trim_set argument. If the first character in the string matches the first character in the trim_set, ltrim removes it. Then, ltrim looks at the second character in the string. If it matches the second character in the trim_set, ltrim removes it, and so on. When the first character in the string does not match the corresponding character in the trim_set, ltrim returns the string and evaluates the next row.
In the example of H. Bender, H does not match either character in the trim_set argument, so ltrim returns the string in the LAST_NAME column and moves to the next row.
Tips for ltrim
Use ltrim with CONCAT to remove leading blank spaces after you concatenate two strings.
You can also remove multiple sets of characters by nesting ltrim. For example, to remove leading blank spaces and the character 'T' from a column of names, you might create an expression as follows:
sff:ltrim( sff:ltrim( NAMES ), 'T' )
round (Numbers)
Rounds numbers to a specified number of digits or decimal places. You can also use round to round dates.
The Round function behaves as follows:
•Returns the number without the fractional part that is closest to the argument.
•If there are two numbers that are close to the argument, then the number that is closest to the positive infinity is returned.
•If the data type of the argument is one of the four numeric data types, xs:float, xs:double, xs:decimal, or xs:integer, then the data type of the result is the same as the data type of the argument.
•If the data type of the argument is a data type derived from one of the numeric data types, then the result is an instance of the base numeric data type.
Syntax
fn:round(arg)
The following table describes the argument for this command:
Argument
Required/
Optional
Description
arg
Required
Must be a numeric data type. You can enter any valid transformation expression. Use operators to perform arithmetic before you round the values.
To pass a NULL value, you must specify an empty sequence in the following format: ()
The function rounds to the nearest integer, truncating the decimal portion of the number. For example, round(12.99) returns 13 and round(15.20) returns 15.
Return Value
Numeric value.
If one of the arguments is NULL, round returns NULL.
Example 1. Example
The following expression returns the rounded values in the Price column.
fn:round(PRICE)
The following table lists some sample values and return values:
PRICE
RETURN VALUE
12.99
13.0
-15.99
-16.0
-18.99
-19.0
56.95
57.0
NULL
NULL
If you want to be more specific with the precision and round the number to the nearest integer or truncate the decimal portion, it is recommended to use round-half-to-even function.
fn:round-half-to-even(arg, precision)
If you enter a positive precision, the function rounds to this number of decimal places. For example, round(12.99, 1) returns 13.0 and round(15.44, 1) returns 15.4.
If you enter a negative precision, the function rounds this number of digits to the left of the decimal point, returning an integer. For example, round(12.99, -1) returns 10 and rounds(15.99, -1) returns 20.
The value returned is the nearest, that is, numerically closest value to the argument that is a multiple of ten to the power of minus precision. If two such values are equally near, that is, if the fractional part in the argument is exactly .500..., the function returns the one whose least significant digit is even.
Example
The following expression returns the values in the Price column rounded to three decimal places.
fn:round-half-to-even(PRICE, 3)
The following table lists some sample values and return values:
PRICE
RETURN VALUE
12.9936
12.994
15.9949
15.995
-18.8678
-18.868
56.9561
56.956
NULL
NULL
You can round digits to the left of the decimal point by passing a negative integer in the precision argument:
fn:round-half-to-even(PRICE, -2)
The following table lists some sample values and return values:
PRICE
RETURN VALUE
13242.99
13200.0
1435.99
1400.0
-108.95
-100.0
NULL
NULL
If you pass zero in the precision argument, the function rounds to the nearest integer:
fn:round-half-to-even(PRICE, 0)
The following table lists some sample values and return values:
PRICE
RETURN VALUE
12.99
13.0
-15.99
-16.0
-18.99
-19.0
56.95
57.0
NULL
NULL
rtrim
Removes blank characters or characters from the end of a string.
If you do not specify a trim_set parameter in the expression, rtrim removes both single-byte and double-byte spaces from the end of a string.
If you use rtrim to remove characters from a string, rtrim compares the trim_set to each character in the string argument, character-by-character, starting with the right side of the string. If the character in the string matches any character in the trim_set, rtrim removes it. The rtrim function continues comparing and removing characters until it fails to find a matching character in the trim_set. It returns the string without the matching characters.
Syntax
sff:rtrim(str, trim_set)
The following table describes the arguments:
Argument
Required/
Optional
Description
string
Required
Any string value. Passes the values that you want to trim. You can enter any valid transformation expression. Use operators to perform comparisons or concatenate strings before removing blank characters from the end of a string.
You must enclose the string value within single or double quotation marks.
To pass a NULL value, you must specify an empty sequence in the following format: ()
trim_set
Optional
Any string value. Passes the characters that you want to remove from the end of the string. You can also enter a text literal.
You must enclose the string value within single or double quotation marks.
To pass a NULL value, you must specify an empty sequence in the following format: ()
The rtrim function is case sensitive. For example, if you want to remove the 'o' character from the string 'Alfredo', you would enter 'o', not 'O'.
Return Value
String. The string values with the specified characters in the trim_set argument removed.
NULL if a value passed to rtrim is NULL. If the trim_set is NULL, rtrim returns NULL.
Example
The following expression removes the characters ‘re’ from the strings in the LAST_NAME column:
sff:rtrim( LAST_NAME, 're')
The following table lists some sample values and return values:
LAST_NAME
RETURN VALUE
Nelson
Nelson
Page
Pag
Osborne
Osborn
NULL
NULL
Sawyer
Sawy
H. Bender
H. Bend
Steadman
Steadman
The rtrim function removes ‘e’ from Page even though ‘r’ is the first character in the trim_set. This is because rtrim searches, character-by-character, for the set of characters you specify in the trim_set argument. If the last character in the string matches the first character in the trim_set, rtrim removes it. If, however, the last character in the string does not match, rtrim compares the second character in the trim_set. If the second from last character in the string matches the second character in the trim_set, rtrim removes it, and so on. When the character in the string fails to match the trim_set, rtrim returns the string and evaluates the next row.
In the last example, the last character in Nelson does not match any character in the trim_set argument, so rtrim returns the string 'Nelson' and evaluates the next row.
Tips for rtrim
Use rtrim with CONCAT to remove trailing blank characters after you concatenate two strings.
You can also remove multiple sets of characters by nesting rtrim. For example, to remove trailing blank characters and the character ‘t’ from the end of each string in a column of names, you might create an expression similar to the following:
sff:trim( sff:rtrim( NAMES ), 't' )
toChar (Numbers)
Converts numeric values to text strings.
Syntax
sff:toChar(xs:double(val))
Note: After you add the function, you must manually add the phrase (xs:double) in the syntax. Otherwise, the taskflow fails.
The following table describes the arguments:
Argument
Required/ Optional
Description
val
Required
The numeric value that you want to convert to a string. You can enter any valid transformation expression.
toChar converts double values to text strings as follows:
•Converts double values of up to 16 digits to strings and provides accuracy up to 15 digits. If you pass a number with more than 15 digits, toChar rounds the number based on the sixteenth digit and returns the string representation of the number in scientific notation. For example, 1234567890123456 double value converts to '1.23456789012346e+015' string value.
•Returns decimal notation for numbers in the ranges (-1e16,-1e-16] and [1e-16, 1e16). toChar returns scientific notation for numbers outside these ranges. For example, 10842764968208837340 double value converts to '1.08427649682088e+019' string value.
toChar converts decimal values to text strings as follows:
•If you pass a decimal port to the toChar function and the input value does not have enough digits to match the scale of the decimal port, the toChar function appends zeros to the value.
For example, if the scale of the decimal port is 5 and the value in a row is 7.6901, the toChar function treats the input value as 7.69010 and the return value is '7.69010.'
Return Value
String.
NULL if a value passed to the function is NULL.
Double Conversion Example
The following expression converts the double values in the SALES port to strings:
sff:toChar(xs:double(SALES))
SALES
RETURN VALUE
1010.99
'1010.99'
-15.62567
'-15.62567'
10842764968208837340
'1.08427649682088e+019' (rounded based on the 16th digit and returns the value in scientific notation)
236789034569723
'236789034569723'
0
'0'
33.15
'33.15'
NULL
NULL
Decimal Conversion Example
The following expression converts the decimal values in the SALES port to strings in high precision mode:
sff:toChar(xs:double(SALES))
The following table lists some sample values and return values when the precision is greater than 38:
SALES
RETURN VALUE
2378964536789761
'2378964536789761'
1234567890123456789012345679
'1234567890123456789012345679'
1.234578945469649345876123456
'1.234578945469649345876123456'
0.999999999999999999999999999
'0.999999999999999999999999999'
12345678901234567890123456799
'12345678901234567890123456799'
23456788992233456678458934567123465239
'23456788992233456678458934567123465239'
423456789012345678901234567991234567899
(greater than 38)
'4.23456789012346e+038'
The following table lists some sample values and return values when the precision is greater than 28:
SALES
RETURN VALUE
2378964536789761
'2378964536789761'
1234567890123456789012345679
'1234567890123456789012345679'
1.234578945469649345876123456
'1.234578945469649345876123456'
0.999999999999999999999999999
'0.999999999999999999999999999'
12345678901234567890123456799
(greater than 28)
'1.23456789012346e+028'
The toChar (Dates) function converts a Date/Time datatype to a string with the format you specify. You can convert the entire date or a part of the date to a string.
Note: Use double quotation marks to separate ambiguous format strings, for example D"D""DDD. The empty quotation marks do not appear in the output.
toDate
Converts a character string to a Date/Time data type. You use the toDate format strings to specify the format of the source strings.
The output port must be Date/Time for toDate expressions.
If you are converting two-digit years with toDate, use either the RR or YY format string. Do not use the YYYY format string.
Syntax
date:toDate(xs:dateTime('date'), 'format')
Note: You must manually add the xs:dateTime phrase and enclose the date values within single quotation marks.
The following table describes the arguments:
Argument
Required/
Optional
Description
date
Required
Must be a string data type. Passes the values that you want to convert to dates. You can enter any valid transformation expression.
format
Required
Enter a valid toDate format string. The format string must match the parts of the dare argument. For example, if you pass the date 'Mar 15 1998 12:43:10AM', you must use the format string 'MON DD YYYY HH12:MI:SSAM'.
Return Value
Date.
The toDate function always returns a date and time. If you pass a string that does not have a time value, the date returned always includes the time 00:00:00.000000000. You can map the results of this function to any target column with a datetime data type.
The following table lists some sample values and return values:
DATE_STR
RETURN_VALUE
'12/31/1999 3783'
12/31/1999 01:02:03
'09/15/1996 86399'
09/15/1996 23:59:59
toDecimal
Converts a string or numeric value to a decimal value. The toDecimal function ignores leading spaces.
Syntax
util:toDecimal(value, scale)
The following table describes the arguments:
Argument
Required/
Optional
Description
value
Required
Must be a string or numeric data type. Passes the value that you want to convert to decimals.
You can enter any valid transformation expression.
scale
Optional
Must be an integer literal between 0 and 28, inclusive. Specifies the number of digits allowed after the decimal point. If you omit this argument, the function returns a value with the same scale as the input value.
This argument is required in advanced mode.
Return Value
Decimal of precision and scale between 0 and 28, inclusive.
0 if the value in the selected column is an empty string or a non-numeric character.
NULL if a value passed to the function is NULL.
Example
This expression uses values from the column IN_TAX. The data type is decimal with precision of 10 and scale of 3:
util:toDecimal(IN_TAX, 3)
The following table lists some sample values and return values:
IN_TAX
RETURN VALUE
'15.6789'
15.679
'60.2'
60.200
'118.348'
118.348
NULL
NULL
'A12.3Grove'
0
This expression uses values from the column Sales. The data type is decimal with precision of 10 and scale of 2:
util:toDecimal(Sales, 2)
The following table lists some sample values and return values:
Sales
RETURN VALUE
'1234'
1234
'1234.01'
1234.01
If you want the return value in 1234.00 format, you can use the following expression:
format-number(util:toDecimal('1234', 2), '0.00')
Decimal overflow
If the size of the number on the left hand side of the decimal point exceeds the precision, the decimal operation overflows.
To resolve this, modify the scale and/or precision of the expression port and connect downstream ports of the mapping to accommodate the size of the input data in the expression.
For example:
If a numeric field is defined to be size 28 with scale of 15, this accepts 13 numbers on the left side of the decimal and 15 on the right. So the following numbers would be valid for a number of 28,15:
1234567890123.11143
13575.123451234567891
However, these numbers would cause a decimal overflow error:
111112222233333.4444
123.1111122222333334
toInteger
Converts a string or numeric value to an integer. The toInteger syntax contains an optional argument that you can choose to round the number to the nearest integer or truncate the decimal portion. The toInteger function ignores leading spaces.
Syntax
util:toInteger(value, flag)
The following table describes the arguments:
Argument
Required/
Optional
Description
value
Required
Must be a string or numeric data type. Passes the value that you want to convert to an integer.
You can enter any valid transformation expression.
flag
Optional
Specifies whether to truncate or round the decimal portion. The flag must be an integer literal or the constants TRUE or FALSE:
- toInteger truncates the decimal portion when the flag is TRUE or a number other than 0.
- toInteger rounds the value to the nearest integer if the flag is FALSE or 0 or if you omit this argument.
Return Value
Integer.
NULL if a value passed to the function is NULL.
0 if a value passed to the function contains alphanumeric characters.
Example
The following expressions use values from the column IN_TAX:
util:toInteger(IN_TAX, fn:boolean(1))
The following table lists some sample values and return values:
IN_TAX
RETURN VALUE
'15.6789'
15
'60.2'
60
'118.348'
118
NULL
NULL
'A12.3Grove'
0
' 123.87'
123
'-15.6789'
-15
'-15.23'
-15
trunc (Numbers)
Truncates numbers to a specific digit based on the number of decimal places specified by the precision.
Syntax
Util:trunc(arg, precision)
Argument
Required/
Optional
Description
arg
Required
Numeric data type. Passes the argument that you want to truncate. You can enter any valid expression that evaluates to a Numeric data type.
precision
Optional
Can be a positive or negative integer. You can enter any valid expression that evaluates to an integer. The integer specifies the number of digits to truncate.
If precision is a positive integer, trunc returns arg with the number of decimal places specified by precision. If precision is a negative integer, trunc changes the specified digits to the left of the decimal point to zeros. If you omit the precision argument, trunc truncates the decimal portion of arg and returns an integer.
All trailing zeros after the decimal point in an argument will be truncated. For example, the following expression returns 2345.7535 as the result:
util:trunc(2345.75350000, 6)
If a number contains more than 16-digits after the decimal point, the result returns an exponential value due to an XQuery limitation.
For example, the following expression returns 1.234567812345679E7 as the result:
util:trunc(12345678.12345678901234567890, 15)
If you pass a decimal precision value, the arg is rounded to the nearest integer before evaluating the expression.
Return Value
Numeric or integer value based on the parameters provided in the function.
Example
The following expressions truncate the values in the PRICE column:
Util:trunc(PRICE, 3)
PRICE
RETURN VALUE
12.9995
12.999
-18.8652
-18.865
56.9563
56.956
15.9928
15.992
Util:trunc(PRICE, -1)
PRICE
RETURN VALUE
12.99
10
-187.86
-180
56.95
50
1235.99
1230
Util:trunc(PRICE )
PRICE
RETURN VALUE
12.99
12
-18.99
-18
56.95
56
15.99
15
trunc (Dates)
Truncates dates to a specific year, month, day, hour, minute, second, or millisecond. You can also use trunc to truncate numbers.
You can truncate the following date parts:
•Year. If you truncate the year portion of the date, the function returns Jan 1 of the input year with the time set to 00:00:00.000000000. For example, the following expression returns 1/1/1997 00:00:00.000000000:
•Month. If you truncate the month portion of a date, the function returns the first day of the month with the time set to 00:00:00.000000000. For example, the following expression returns 4/1/1997 00:00:00.000000000:
•Day. If you truncate the day portion of a date, the function returns the date with the time set to 00:00:00.000000000. For example, the following expression returns 6/13/1997 00:00:00.000000000:
•Hour. If you truncate the hour portion of a date, the function returns the date with the minutes, seconds, and subseconds set to 0. For example, the following expression returns 4/1/1997 11:00:00.000000000:
•Minute. If you truncate the minute portion of a date, the function returns the date with the seconds and subseconds set to 0. For example, the following expression returns 5/22/1997 10:15:00.000000000:
•Second. If you truncate the second portion of a date, the function returns the date with the milliseconds set to 0. For example, the following expression returns 5/22/1997 10:15:29.000000000:
•Millisecond. If you truncate the millisecond portion of a date, the function returns the date with the microseconds set to 0. For example, the following expression returns 5/22/1997 10:15:30.135000000: