String Functions for Formulas

Use string functions to create values based on other strings. For example, you can change the casing of text strings, concatenate values from multiple columns, and replace values. The arguments for string functions must be text strings or dimension fields.

When you enter a formula, use the API name of the column in the expression, not the label. The parameters section is surrounded by parentheses. Also, text strings must be enclosed in single straight quotes ('This is a string.').

For example, you can nest string functions in an expression to combine the sales territory and country into a single column. Then you can apply title casing to the results. title(concat(Sales_Territory, ' ',Country))

You can use the following string functions in a formula expression.

String Function	Description	Syntax
`char_length`	Returns the number of characters in the specified string. Leading and trailing whitespace characters are included in the length returned.	`char_length(field)` `field` is the dimension field or text string to measure. Example: A point-of-service checkout tool generates receipts with a maximum number of characters. Evaluate your product names to see their length. `char_length(product_name)`
`concat`	Returns a string by merging the values of the specified columns and input strings.	`concat(field1,field2,...fieldN)` `field1` is the first dimension field or text string to include in the concatenated value. `field2` is the second dimension field or text string to include. `fieldN` is any number of extra dimension fields or text strings to include. Example: To create the full name, concatenate the first and last names with a space in between them. `concat("OwnerId.FirstName",' ',"OwnerId.LastName")`
`endsWith`	Returns true if the specified string is found at the end of the column value. This function is only supported in `case` functions because it returns as a boolean.	`ends_with(fieldName,literal`) `fieldName` is the dimension field to be searched. `literal` is the value to search for at the end of the field.
`instr`	Returns the (1-based) first location of the value 'searchString' in the specified 'field' column. Optionally, start the search at a different location with 'position', or return the location of a different searchString instance with 'occurrence'.	`instr(field, searchString, [position, [occurrence]])` `field` is the dimension field or text string that is being evaluated to find the `searchString`. `searchString` is the dimension field or text string that is being searched for in the `field`. `position` is the start location in `field` where the search begins. This parameter is optional. If you don't specify a position, the search begins at position 1. `occurrence` is the specific instance of the `searchString` to return. This parameter is optional. Use `position`, too, if you use this parameter. If you don't specify a position, the search begins at position 1. Example: Evaluate a string for an exclamation point. `instr('123!456!78!', !) --The result is 4 instr('123!456!8!', !, [4]) --The result is 7 instr('123!456!8!', !, [4, [2]]) --The result is 9`
`lower`	Returns the string with all characters in lowercase. If the input string is null, then the result is null.	`lower(field)` `field` is the dimension field or text string to convert to lowercase. Example: The Account.Industry column uses different casing for industries, like “Media” and “media.” To apply consistent casing to the columns values, convert them to lowercase. `lower("Account.Industry")`
`ltrim`	Removes the specified substring from the beginning of a string. If no substring is provided, remove the leading space characters from a string.	`ltrim(field,valueToBeRemoved`) `field` is the dimension field or text string to remove the leading spaces or specified substring from. `valueToBeRemoved` is the value to remove from the string. This parameter is optional. If you don't specify a valueToBeRemoved, the transformation removes the leading spaces. Example: Trim the leading space characters from a string. `ltrim(' 5 Spaces Before')`
`replace`	Replaces a substring with another string. If any of the arguments are null, then the function returns null. This function is case-sensitive.	`replace(field,searchString,replacementString)` `string` is the dimension field or text string that contains the substring to be replaced. `searchString` is the substring to replace. If searchString is an empty string, the function returns null. `replacementString` is the value that replaces the substring. Example: Change the account name from “salesforce.com” to “Salesforce” in the Account_Name column. `replace(Account_Name,'salesforce.com','Salesforce')`
`rtrim`	Removes the specified substring from the end of a string. If no substring is provided, remove the trailing space characters from the end of a string.	`rtrim(field,valueToBeRemoved`) `field` is the dimension field or text string to remove the trailing spaces or specified substring from. `valueToBeRemoved` is the value to remove from the string. This parameter is optional. If you don't specify a valueToBeRemoved, the transformation removes the trailing spaces. Example: Trim the 2 trailing space characters from a string. `rtrim('2 Spaces After ')`
`startsWith`	Returns true if the specified string is found at the beginning of the column value. This function is only supported in `case` functions because it returns as a boolean.	`startsWith(fieldName,literal`) `fieldName` is the dimension field to be searched. `literal` is the value to search for at the beginning of the field.
`string`	Converts the date or number value to a string data type.	`string(expression)` `expression` is the date or number field to convert.
`substr`	Returns characters from the string, starting at the specified position and of the specified length. Also called `substring`.	`substr(field,position,len)` `field` is the dimension field or text string to extract the substring from. `position` is the starting character position of the substring. The first character in a string is at position 1. If position is negative, then the position is relative to the end of the string. A position of -1 denotes the last character. `len` is the length, or number of characters, to return. If len is 0, the output is an empty string. If len is negative, then the function returns null. This parameter is optional. Example: Return the 13th, 14th, and 15th character from each account ID. `substr(Account_Id,13,3)`
`title`	Returns the string with the initial character of every word in uppercase and the remaining characters in lowercase. For example, “united states” becomes “United States.”	`title(field)` `field` is the dimension field or text string on which to apply title casing. Example: Apply title casing on the opportunity names to ensure consistent casing on these values. `title(Opp_Name)`
`trim`	Removes the specified substring from the beginning and end of a string. If no string is specified, remove the space characters from the beginning and end of a string.	`trim(field,valueToBeRemoved)` `field` is the dimension field or text string to remove the specified substring from. `valueToBeRemoved` is the value removed from the string. This parameter is optional. If you don't specify a valueToBeRemoved, the transformation removes the leading and trailing spaces. Example: Remove the two leading spaces before and after a string. `trim(' Spaces Before and After ')`
`upper`	Returns the string with all characters in uppercase. If string is null, then the result is null.	`upper(field)` `field` is the dimension field or text string to convert to uppercase. Example: You have a column First_Name that accepts freeform entry for users to enter their first name. As a result, some names are in uppercase, some in lowercase, and others in mixed case. Convert them to all to uppercase characters to make the casing consistent. `upper(First_Name)`

Note

Functions that don't handle null values explicitly won’t convert NULL values to be empty (for strings) or 0 (for numeric). For example, a concat output returns NULL if any of the concatenated fields have a null value on that row. So if the formula looks like concat(first_name, last_name), the returned data with a NULL value for last_name is:

First Name | Last Name | Output
          Alice      | Smith     | Alice Smith
          Bob        | NULL      | NULL

To handle null values, you can use the concat function with another function. For example: concat(first_name, coalesce(last_name, ''))

Notation for String Formulas
In Salesforce Data Pipelines, use formulas to create custom calculations. If you manually type your formulas, use single quotes ' for string literals and double quotes " for joined fields with dot notation or reserved keywords. If you use the recipe builder tool instead of typing, the correct single or double quote is automatically used.
Reserved Keywords in Formulas
In Salesforce Data Pipelines, Data Prep has reserved keywords that need special handling when manually writing formulas. Use double quotes " when referencing dataset columns that share a name with reserved keywords. To avoid cross-referencing your column names to this list, the best practice is to use double quotes around all column names.

Cet article a-t-il résolu votre problème ?

Dites-nous ce que nous pouvons améliorer !

String Functions for Formulas

General Information

Required Cookies

Functional Cookies

Advertising Cookies

General Information

Required Cookies

Functional Cookies

Advertising Cookies

Cookie List

Gamme de produits

Impact des fonctionnalités

Edition

Expérience

String Functions for Formulas