How to use Talend system routines

This post gives you an overview of the most commonly used Talend system routines. In this post, routines follow the order in which they are displayed in the Repository. They are grouped according to their types. Each type is detailed in a different section:

  1. Numeric routines
  2. Relational routines
  3. StringHandling routines
  4. TalendDataGenerator routines
  5. TalendDate routines
  6. TalendString routines

 

1. Numeric routines

Numeric routines allow you to return whole or decimal numbers in order to use them as settings in one or more Job components. To add numeric IDs, for instance.

RoutineDescriptionSyntax
sequenceReturns an incremental numeric ID.Numeric.sequence("Parameter name", start value,increment value)
resetSequenceCreates a sequence if it doesn't exist and attributes a new start value.Numeric.resetSequence (Sequence Identifier,startvalue)
removeSequenceRemoves a sequence.Numeric.RemoveSequence (Sequence Identifier)
randomReturns a random whole number between the maximum and minimum values.Numeric.random(minimum start value, maximum end value)
convertImplied
DecimalFormat
Returns a decimal with the help of an
implicit decimal model.
Numeric.convertImpliedDecimalFormat ("Target Format", value to be converted)

The three routines sequence, resetSequence, and removeSequence are closely related.
• The sequence routine is used to create a sequence identifier, named s1 by default, in the Job. This sequence identifier is global in the Job.
• The resetSequence routine can be used to initialize the value of the sequence identifier created by sequence routine.
• The removeSequence routine is used to remove the sequence identifier from the global variable list in the Job.

2. Relational routines

Relational routines allow you to check affirmations based on booleans.

RoutineDescriptionSyntax
ISNULLChecks if the variable provided is a null value.
It returns true if the value is NULL and false if the value is not NULL.
Relational.ISNULL(variable)
NOTReturns the complement of the logical value of an expression.Relational.NOT(expression)
isNullChecks if the variable provided is a null value.
It returns 1 if the value is NULL and 0 if the value is not NULL.
Relational.isNull(variable)

3. StringHandling routines

The StringHandling routines allow you to carry out various kinds of operations and tests on alphanumeric expressions, based on Java methods.

RoutineDescriptionSyntax
ALPHAChecks whether the expression
is arranged in alphabetical order.
Returns the true or false boolean
accordingly.
StringHandling.ALPHA("string to be checked")
IS_ALPHAChecks whether the expression
contains alphabetical characters
only, or otherwise. Returns the
true or false boolean accordingly.
StringHandling.IS_ALPHA("string to be checked")
CHANGEReplaces an element of a
string with a defined replacement
element and returns the new
string.
StringHandling.CHANGE("string to be checked", "string to be replaced","replacement string")
COUNTReturns the number of times a
substring occurs within a string.
StringHandling.COUNT("string to be checked","substring to be counted")
DOWNCASEConverts all uppercase letters in
an expression into lowercase and
returns the new string.
Returns the position of the first
character in a specified substring,
within a whole string. If the
substring specified does not exist
in the whole string, the value - 1
is returned.
UPCASEConverts all lowercase letters in
an expression into uppercase and
returns the new string.
StringHandling.UPCASE("string to be converted")
DQUOTEEncloses an expression in double
quotation marks.
StringHandling.DQUOTE("string to be enclosed in double quotation marks")
EREPLACESubstitutes all substrings that
match the given regular
expression in the given old string
with the given replacement and
returns a new string.
StringHandling.EREPLACE(oldStr, regex, replacement)
INDEXReturns the position of the first
character in a specified substring,
within a whole string. If the
substring specified does not exist
in the whole string, the value - 1
is returned.
StringHandling.INDEX("string to be checked","substring specified")
LEFTSpecifies a substring which
corresponds to the first n
characters in a string.
StringHandling.LEFT("string to be checked", number of characters)
RIGHTSpecifies a substring which
corresponds to the last n
characters in a string.
StringHandling.RIGHT("string to be checked", number of characters)
LENCalculates the length of a string.StringHandling.LEN("string to check")
SPACEGenerates a string consisting of a
specified number of blank spaces.
StringHandling.SPACE(number of blank spaces to be generated)
SQUOTEEncloses an expression in single
quotation marks.
StringHandling.SQUOTE("string to be enclosed in single quotation marks")
STRGenerates a particular character a
the number of times specified.
StringHandling.STR('character to be generated',number of times)
TRIMDeletes the spaces and tabs before
the first non-blank character in a
string and after the last non-blank
character, then returns the new
string.
StringHandling.TRIM("string to be checked")
BTRIMDeletes all the spaces and tabs
after the last non-blank character
in a string and returns the new
string.
StringHandling.BTRIM("string to be checked")
FTRIMDeletes all the spaces and tabs
preceding the first non-blank
character in a string.
StringHandling.FTRIM("string to be checked")
SUBSTRReturns a portion of a string. It
counts all characters, including
blanks, starting at the beginning of
the string.
StringHandling.SUBSTR(string, start, length)
• string: the character string you want to search.
• start: the position in the string where you want to start counting.
• length: the number of characters you want to return
LTRIMRemoves blanks or characters
from the beginning of a string.
StringHandling.LTRIM(string[, trim_set])
• string: the string you want to change.
• trim_set: the characters you want to remove from the beginning of the string. LTRIM will compare the trim_set to the string character-bycharacter,
starting with the left side of the string, and remove characters until it fails to find a matching character in the trim_set. If this parameter is not specified, LTRIM will remove any blanks from the beginning of
the string.
RTRIMRemoves blanks or characters
from the end of a string.
StringHandling.RTRIM(string[, trim_set])
• string: the string you want to change.
• trim_set: the characters you want to remove from the ending of the string. RTRIM will compare the trim_set to the string character-by-character, starting with the right side of the string, and remove characters until it fails to find a matching character in the trim_set. If this parameter is not specified, RTRIM will remove any blanks from the ending of the string.
LPADConverts a string to a specified
length by adding blanks or
characters to the beginning of the
string.
StringHandling.LPAD(first_string, length[,
second_string])
• first_string: the string you want to change.
• length: the length you want the string to be after being padded.
• second_string: the characters you want to append to the left side of the first_string.
RPADConverts a string to a specified
length by adding blanks or
characters to the end of the string.
StringHandling.RPAD(first_string, length[,
second_string])
• first_string: the string you want to change.
• length: the length you want the string to be after being padded.
• second_string: the characters you want to append to the right side of the first_string.
INSTRReturns the position of a character
set in a string, counting from left
to right and starting from 1.
Note that it returns 0 if the search
is unsuccessful and NULL if the
search value is NULL.
StringHandling.INSTR(string,search_value, start, occurrence)
• string: the string you want to search.
• search_value: the set of characters you want to search for.
• start: the position in the string where you want to start the search. The default is 1, meaning it starts the search from the first character in the string.
• occurrence: the occurrence you want to search for.
For example, StringHandling.INSTR("Talend Technology", "e",3, 2), it will start the search from the third character l and return 7, the position of the second character e.
TO_CHARConverts numeric values to text
strings.
StringHandling.TO_CHAR(numeric_value)

4. TalendDataGenerator routines

The TalendDataGenerator routines are functions which allow you to generate sets of test data. They are based on fictitious lists of first names, second names, addresses, towns and States provided by Talend. These routines are generally used when developing Jobs, using a tRowGenerator, for example, to avoid using production or company data.

RoutineDescriptionSyntax
getFirstNamereturns a first name taken randomly from a fictitious list.TalendDataGenerator.getFirstName()
getLastNamereturns a random surname from a fictitious list.TalendDataGenerator.getLastName()
getUsStreetreturns an address taken randomly from a list of common American street names.TalendDataGenerator.getUsStreet()
getUsCityreturns the name of a town taken randomly from a list of American towns.TalendDataGenerator.getUsCity()
getUsStatereturns the name of a State taken randomly from a list of American States.TalendDataGenerator.getUsState()
getUsStateIdreturns an ID randomly taken from a list of IDs attributed to American States.TalendDataGenerator.getUsStateId()

5. TalendDate routines

The TalendDate routines allow you to carry out different kinds of operations and checks concerning the format of Date expressions.

RoutineDescriptionSyntax
addDateAdds n days, n months, n hours, n minutes or n seconds to a Java date and returns the new date. The Date format is: "yyyy", "MM","dd", "HH", "mm", "ss" or "SSS".TalendDate.addDate("String date initiale", "format Date - eg.: yyyy/MM/dd", whole n,"format of the part of the date to which n is to be added -eg.:yyyy").
compareDateCompares all or part of two dates according to the format specified. Returns 0 if the dates are identical, -1 if the first date is earlier and 1 if the second date is earlier.TalendDate.compareDate (Date date1, Date date2, "format to be compared - eg.: yyyy-MM-dd")
diffDateReturns the difference between two dates in terms of days, months or years according to the comparison parameter specified.TalendDate.diffDate(Date1(), Date2(), "format of the part of the date to be compared -eg.:yyyy")
diffDateFloorReturns the difference between two dates by floor in terms of years, months, days, hours, minutes, seconds or milliseconds according to the comparison parameter specified.TalendDate.diffDateFloor(Date1(),Date2(), "format of the part of the date to be compared - eg.:MM")
formatDateReturns a date string which corresponds to the format specified.TalendDate.formatDate("date format - eg.: yyyy-MMdd HH:mm:ss", Date() to be formatted
formatDateLocaleChanges a date into a date/hour string according to the format used in the target country.TalendDate.formatDateLocale("format target", java.util.Date date, "language or country code")
getCurrentDateReturns the current date. No entry
parameter is required.
TalendDate.getCurrentDate()
getDateReturns the current date and hour in the format specified (optional). This string can contain fixed character strings or variables linked to the date. By default, the string is returned in the format, DD/ MM/CCYY.TalendDate.getDate("Format of the string - ex: CCYYMM-DD")
getFirstDayOfMonthChanges the date of an event to the first day of the current month and returns the new date.TalendDate.getFirstDayMonth(Date)
getLastDayOfMonthChanges the date of an event to the last day of the current month and returns the new date.TalendDate.getLastDayMonth(Date)
getPartOfDateReturns part of a date according to the format specified. This string can contain fixed character strings or variables linked to the date.TalendDate.getPartOfDate("String indicating the part of the date to be retrieved, "String in the format of the date to be parsed")
getRandomDateReturns a random date, in the ISO format.TalendDate.getRandomDate("format date of the character string", String minDate, String maxDate)
isDateChecks whether the date string corresponds to the format specified. Returns the boolean value true or false according to the outcome.TalendDate.isDate(Date() to be checked, "format of the date to be checked - eg.: yyyy-MM-dd HH:mm:ss")
parseDateChanges a string into a Date. Returns a date in the standard format.TalendDate.parseDate("format date of the string to be parsed", "string in the format of the date to be parsed")
parseDateLocaleParses a .string according to a specified format and extracts the date. Returns the date according to the local format specified.TalendDate.parseDateLocale("date format of the string to be parsed", "String in the format of the date to be parsed", "code corresponding to the country or language")
setDateModifies part of a date according to the part and value of the date specified and the format specified.TalendDate.setDate(Date, whole n, "format of the part of the date to be modified -eg.:yyyy")
TO_CHARConverts a date to a character string.TalendDate.TO_CHAR(date[,format])
• date: the date value you want to convert to a character string.
• format: the string which defines the format of the return value.
TO_DATEConverts a character string to a Date/Time datatype.TalendDate.TO_DATE(string[, format])
• string: the string you want to convert to a Date/Time datatype.
• format: the format string that matches the part of the string argument. If not specified, the string value must be in the date format MM/dd/yyyy HH:mm:ss.SSS. For example, TalendDate.TO_DATE("04/24/2017 13:55:42.123") will return Mon Apr 24 13:55:42 CST 2017.
ADD_TO_DATEAdds 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.TalendDate.ADD_TO_DATE(date, format, amount)
• date: the date value you want to change.
• format: the format string specifying the portion of the date value you want to change.
• Valid format strings for year: Y, YY, YYY, and YYYY.
• Valid format strings for month: MONTH, MM, and MON.
• Valid format strings for day: D, DD, DDD, DAY, and DY.
• Valid format strings for hour: HH, HH12, and HH24.
• Valid format string for minute: MI.
• Valid format string for second SS.
• Valid format string for millisecond: MS.

6. TalendString routines

The TalendString routines allow you to carry out various operations on alphanumerical expressions.

RoutineDescriptionSyntax
replaceSpecialCharForXMLreturns a string from which the special characters (eg.:: <, >, &...) have been replaced by equivalent XML characters.TalendString.replaceSpecialCharForXML ("string containing the special characters -
eg.: Thelma & Louise")
checkCDATAForXMLidentifies characters starting with
]]> as pertaining to XML and
returns them without modification.
Transforms the strings not
identified as XML in a form
which is compatible with XML and returns them.
TalendString.checkCDATAForXML("strng to be parsed")
talendTrimparses the entry string and removes the filler characters from the start and end of the string according to the alignment value specified: -1 for the filler characters at the end of the string, 1 for those at the start of the string and 0 for both. Returns the trimmed string.TalendString.talendTrim("string to be parsed","filler character to be removed", character position)
removeAccentsremoves accents from a string and returns the string without the accents.TalendString.removeAccents("String")
getAsciiRandomStringgenerates a random string with a specific number of characters.TalendString.getAsciiRandomString (whole number indicating the length of the string)

If you are also interested to read about the most common conversions between datatypes you realy have to read this post: how to convert between different datatypes

Leave a Reply

Assign a menu in the Left Menu options.
Assign a menu in the Right Menu options.