Derived Columns String Functions

​

CONCAT (Concatenate)

• Syntax: concat(value_1, value_2, ...)
• About: Joins two or more text strings into one continuous string.
• Example: Combine first and last names in Chargebee data with concat(first_name, " ", last_name).

​

CONTAINS (Text Contains)

• Syntax: contains(string, search_string)
• About: Checks if the first string contains the second string.
• Example: To check if a transaction description contains the word “refund”, use contains(description, "refund").

​

LENGTH (String Length)

• Syntax: length(string)
• About: Returns the number of characters in a string.
• Example: Measure the length of a customer comment with length(comment).

​

LOWER (Convert to Lowercase)

• Syntax: lower(string)
• About: Converts a text string to all lowercase letters.
• Example: Standardize email addresses for comparison with lower(email).

​

POSITION (Find Substring)

• Syntax: position(string, search_string)
• About: Finds the starting position of a substring within a string.
• Example: Locate the position of ”@” in an email address with position(email, "@").

​

REPLACE (Replace Substring)

• Syntax: replace(string, old_string, new_string)
• About: Replaces occurrences of a specified substring within a string with a new substring.
• Example: Correct a common misspelling in customer feedback with replace(feedback, "recieved", "received").

​

SUBSTRING (Extract Substring)

• Syntax: substring(string, start_position, length)
• About: Extracts a substring from a string starting at a specified position for a certain length.
• Example: To extract a product code from a string, use substring(product_info, 4, 8) where the code starts at the 4th character and is 8 characters long.

​

UPPER (Convert to Uppercase)

• Syntax: upper(string)
• About: Converts a text string to all uppercase letters.
• Example: For a Chargebee invoice reference to be displayed in uppercase, use upper(invoice_ref).

​

FIND (Find Text Position)

• Syntax: FIND(search_for, text_to_search, [starting_at])
• About: Returns the position at which a string is first found within another string.
• Example: To find where a customer’s country code appears in an address, FIND("US", address) might be used. Returns the position of US in Address column string.

​

TRIM (Trim Spaces)

• Syntax: TRIM(text)
• About: Removes any leading and trailing spaces from a text string.
• Example: Trim excess spaces from customer-entered names with TRIM(customer_name).