Text
LEFT
Description
Returns the leftmost characters from a text field or expression result. The number of characters returned is indicated by the numeric field or expression specified by LENGTH OF CHARACTERS.
Syntax
LEFT <Text field or Expression> NUMBER OF CHARACTERS <Numeric field or expression>
Supported values
Only text values can be used for LEFT. Only numeric values can be used for NUMBER OF CHARACTERS
NULL handling
If any of the inputs is NULL, the result is NULL.
Example
LEFT Case type NUMBER OF CHARACTERS 5
RIGHT
Description
Returns the rightmost characters from a text field or expression result. The number of characters returned is indicated by the numeric field or expression specified by LENGTH OF CHARACTERS.
Syntax
RIGHT <Text field or Expression> NUMBER OF CHARACTERS <Numeric field or expression>
Supported values
Only text values can be used for RIGHT. Only numeric values can be used for NUMBER OF CHARACTERS
NULL handling
If any of the inputs is NULL, the result is NULL.
Example
RIGHT Case type LENGTH OF CHARACTERS 5
SUBSTRING
Description
Returns a part of the text field value or expression result. The number of characters to be returned is indicated by the numeric field value or expression result specified in LENGTH OF CHARACTERS and the start position is defined by STARTING FROM CHARACTER.
Syntax
SUBSTRING <Text field or Expression> NUMBER OF CHARACTERS <Numeric field or expression> STARTING FROM CHARACTER <Numeric field or Expression>
Supported values
Only text values can be used for SUBSTRING. Only numeric values can be used for NUMBER OF CHARACTERS and STARTING FROM CHARACTER.
NULL handling
If any of the inputs is NULL, the result is NULL.
Example
SUBSTRING Phonenumber = "1-800-123-4567" NUMBER OF CHARACTERS 3 STARTING FROM CHARACTER 3 (Returns the 3-digit area code 800)
CONCAT
Description
Concatenates two or more text field values or expression results into one text value.
Syntax
CONCAT <Text field or Expression> + <Text field or Expression> [+ <Text field or Expression>]
Supported values
Only text values can be used.
NULL handling
NULL values are not included in the result. For example CONCAT('A', NULL, 'B') returns AB.
Example
CONCAT (Purchase_Order_ID,'-',Purchase_order_item_ID (creates a new CaseID by merging the Purchase_Order_ID and the Purchase-Order_Item ID.)
COALESCE
Description
Returns the first non-NULL value in a list of values defined by expressions, fields, or constants.
Syntax
COALESCE <Expression or Field or Constant> + <Expression or Field or Constant> [+ <Expression or Field or Constant>]
Supported values
All values can be used.
NULL handling
If all values in the list are NULL, then the COALESCE function will return NULL.
Example
COALSCE (Purchase_order_ID, Invoice_ID) (Returns the first non NULL value to check if there are Invoices created without a Purchase order.