Text

LEFT of

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 LEFT.

Syntax

LEFT <Numeric field or Expression> characters of <Text field or expression>

Supported values

Only numeric values can be used for LEFT. Only text values can be used for characters of.

NULL handling

If any of the inputs is NULL, the result is NULL.

Example

LEFT 5 characters of Case type

RIGHT of

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 RIGHT.

Syntax

RIGHT <Numeric field or Expression> characters of <Text field or expression>

Supported values

Only numeric values can be used for RIGHT. Only text values can be used for characters of.

NULL handling

If any of the inputs is NULL, the result is NULL.

Example

RIGHT 5 characters of Case type

SUBSTRING FROM

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 characters long and the start position is defined by start position.

Syntax

SUBSTRING of <Text field or Expression>, <Numeric field or expression> characters long, start position <Numeric field or Expression>

Supported values

Only text values can be used for SUBSTRING. Only numeric values can be used for characters long and start position.

NULL handling

If any of the inputs is NULL, the result is NULL.

Example

SUBSTRING of Phonenumber = "1-800-123-4567", 3 characters long, start position 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.