System functions are built-in functions provided by Data Exchange, which enable you to customize data from a source data store to a target data store based on these functions.
The following system functions are supported in Data Exchange:
If
IfElse
Trim
TrimStart
TrimEnd
Substring
IndexOf
PadLeft
PadRight
Length
ConvertToInt64
ConvertToBoolean
ConvertToDateTime
ConvertToDecimal
ConvertToDouble
IsNull
Description
Returns a boolean which indicates if the input parameter is null or not.
Syntax
IsNull (<<object>>)
Parameters
<object>- an object
Return Value
Type: Boolean
Examples
IsNull (<<intField>>)
IsNull (<<stringField>>)
IsNull ()
If
Description
The If statement tests a condition and returns a value if the condition evaluates to True, and NULL if it evaluates to False.
Syntax:
If(<<ifExpression>>, <<ifTrueResult>>)
Parameters
<<ifExpression>> - an expression evaluates to a Boolean result. It not necessarily has to be a variable, though variables of Boolean type are supported.
<<ifTrueResult>> - value to be returned when <<ifExpression>> is evaluated to True. Otherwise, NULL will be returned.
Return Value
Type: Object
Examples
If(<<booleanVariable>>, 123)
If(<<intValue>> >= 100, <<intValue>>)
If((<<decimalValue>>-100) > 50, <<otherVariable>>)
IfElse
Description
The IfElse statement tests a condition and returns a value if the condition evaluates to True, and another value if the condition evaluates to False.
Syntax
IfElse(<<ifExpression>>, <<ifTrueResult>>,<<elseResult>>)
Parameters
<<ifExpression>> - an expression evaluates to a Boolean result. It not necessarily has to be a variable, though variables of Boolean type are supported.
<<ifTrueResult>> - value to be returned when <<ifExpression>> is evaluated to True.
<<elseResult>> - value to be returned when <<ifExpression>> is evaluated to False.
Return Value
Type: Object
Examples
IfElse(<<booleanVariable>>, 1,0)
IfElse(<<intValue>> >= 100, <<intValue>>, <<anotherIntValue>>)
IfElse(ConvertToBoolean(<<oneVariable>>), <<intValue>>, <<anotherIntValue>>)
Trim
Description
Trims the blank spaces at the beginning and end of the string.
Syntax
Trim(<<string>>)
Parameters
<<string>> - string to be trimmed. It must be of character data type.
Return Value
Type: String
Examples
Trim(<<firstName>>)
Trim(<<firstName>>+<<lastName>>)
Trim(Substring(<<fullName>>, 0, 5))
TrimStart
Description
Trims the leading blank spaces of the string.
Syntax
TrimStart(<<string>>)
Parameters
<<string>> - string to be trimmed. It must be of character data type.
Return Value
Type: String
Examples
TrimStart(<<firstName>>)
TrimStart(<<firstName>>+<<lastName>>)
TrimStart(Substring(<<fullName>>, 0, 5))
TrimEnd
Description
Trims the trailing blank spaces of the string.
Syntax
TrimEnd(<<string>>)
Parameters
<<string>> - string to be trimmed. It must be of character data type.
Return Value
Type: String
Examples
TrimEnd(<<firstName>>)
TrimEnd(<<firstName+<<lastName>>)
TrimEnd(Substring(<<fullName>>, 0, 5))
Substring
Description
Retrieves a substring that starts at a specified character position and has a specified length.
Syntax
Substring(<<string>>, <<startIndex>>, <<length>>)
Parameters
<<string>> - the source string from where the substring is retrieved.
<<startIndex>> - the zero-based index position from where the substring will start.
<<length>> - the total length of the substring.
Return Value
Type: String
Examples
Substring(<<fullName>>, 0, 5)
Substring(Trim(<<firstName>>+<<lastName>>), 7, 10)
Substring(“somestring”, 0, 4)
IndexOf
Description
Returns a zero-based index on the first occurrence of the specified string, or -1 if it does not occur.
Syntax
IndexOf(<<string>>, <<searchString>>)
Parameters
<<string>> - source string.
<<searchString>> - string to search for in source string. Case sensitive when searched in source string.
Return Value
Type: Int64
Examples
IndexOf(“hello,world!”, “hello”)
IndexOf(<<fullNameField>>, “Bob”)
IndexOf(<<anotherStringField>>, “unisys.com”)
PadLeft
Description
Returns a string of a specified length in which the start of the current string is padded with a specified character.
Syntax
PadLeft(<<string>>, <<totalLength>>, <<charToPad>>)
Parameters
<<string>> - string that will be left padded.
<<totalLength>> - the number of characters in the resulting string.
<<charToPad>> - character used to left pad. It must be a single character.
Return Value
Type: String
Examples
PadLeft(<<firstNameField>>, 10, “a”)
PadLeft(<<lastNameField>>, 10, “#”)
PadLeft(<<fullName>>, 20, <<singleCharField>>)
PadRight
Description
Returns a string of a specified length in which the end of the current string is padded with a specified character.
Syntax
PadRight(<<string>>, <<totalLength>>, <<charToPad>>)
Parameters
<<string>> - string that will be right padded.
<<totalLength>> - the number of characters in the resulting string.
<<charToPad>> - character used to right pad. It must be a single character.
Return Value
Type: String
Examples
PadRight(<<firstNameField>>, 10, “a”)
PadRight(<<lastNameField>>, 10, “#”)
PadRight(<<fullName>>, 20, <<singleCharField>>)
Length
Description
Returns the length of the string represented by the specified object.
Syntax
Length(<<object>>)
Parameters
<<object>> - an object.
Return Value
Type: Int64
Examples
Length(<<intField>>)
Length(<<stringField>>)
Length(Substring(<<fullName>>, 0, 10))
ConvertToInt64
Description
Converts a specified object to data of long type.
Syntax
ConvertToInt64(<<object>>)
Parameters
<<object>> - an object.
Return Value
Type: Int64
Examples
ConvertToInt64(123)
ConvertToInt64(<<studentAgeField>>)
ConvertToInt64(<<salaryField>>*<<monthField>>)
ConvertToBoolean
Description
Converts a specified object to an equivalent Boolean value.
Syntax
ConvertToBoolean(<<object>>)
Parameters
<<object>> - an object.
Return Value
Type: Boolean
Examples
ConvertToBoolean(<<bitField>>)
ConvertToBoolean(1)
ConvertToBoolean(<<salary1>> > <<salary2>>)
ConvertToDateTime
Description
Converts a specified object to a DateTime value.
Syntax
ConvertToDateTime(<<object>>, <<formatString>>, <<cultureName>>)
Parameters
<<object>> - an object. For any of the system functions, you can use any object as a parameter. If some of the objects are not directly used as a parameter then use a combination of objects and functions to result in the correct parameter for the system defined function.
For example, to convert a boolean type to datetime, you need to first convert it to an integer and input this value as the object in the system defined function. The converted value should be of length 14 characters.
<<formatString>> - if the source object is of numeric data type (excluding floating point numbers), then, <<formatString>> must be provided.
Currently the supported formats are — “yyyyMMdd”, “yyyyMMddhhmmss”, and “hhmmssnnnnnn”. When using “hhmmssnnnnnn” format, the default year, month, and day are 1900, 01, 01 respectively.
If the source object is of another data type, <<formatString>> is optional. If not required, specify it as an empty string ("").
<<cultureName>> - an optional parameter. The default value is English culture, “en-US”. If you do not want to provide a culture name, specify it as an empty string ("").
Return Value
Type: DateTime
Examples
ConvertToDateTime(<<integerField>>, “yyyyMMdd”, “”)
ConvertToDateTime(<<objectField>>, “”, “”)
ConvertToDateTime(<<cultureDependField>>, “”, “zh-CN”)
ConvertToDecimal
Description
Converts a specified object to a decimal number.
Syntax
ConvertToDecimal(<<object>>)
Parameters
<<object>> - an object.
Return Value
Type: Decimal
Examples
ConvertToDecimal(<<salaryField>>)
ConvertToDecimal(ConvertToDouble(<<intField>>))
ConvertToDecimal(123.45)
ConvertToDouble
Description
Converts a specified object to double.
Syntax
ConvertToDouble(<<object>>)
Parameters
<<object>> - an object.
Return Value
Type: Double
Examples
ConvertToDouble(<<salaryField>>)
ConvertToDouble(<<anotherField>>)
ConvertToDouble(123.45)