Loading
CRM Analytics
Table of Contents
Filter by (0)Add
Select Filters

          No results
          No results
          Here are some search tips

          Check the spelling of your keywords.
          Use more general search terms.
          Select fewer filters to broaden your search.

            Search all of Salesforce Help
            Search all of Salesforce Help
            String Functions for Unconverted Formulas

            You are here:

            1. Salesforce Help
            2. Docs
            3. CRM Analytics

            String Functions for Unconverted 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 columns. If your recipe was upgraded to Data Prep, but your recipe doesn’t convert, this topic is for your unconverted recipe.

            Note
            Note To get a list of string functions for a Data Prep recipe, see String Functions for Formula Fields.

            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))

            When you enter a formula, text strings must be enclosed in single straight quotes (’This is a string.’). Column names must be enclosed in square brackets ([Opportunity_Name]).

            You can use these string functions in a formula expression.

            String Function Description Syntax
            concat Returns a string by concatenating the values of the specified columns and input strings. For example, to display the close date as MM-DD-YYYY, concatenate the Close_Date_Month column, Close_Date_Day column, and Close_Date_Year column, and add a dash between each of them.

            concat(string1,string2,...)

            string1 is the first dimension column or text string to include in the concatenated value.

            string2 is the second dimension column or text string 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])
            lower Returns a string with all characters from the input string in lowercase. If the input string is null, then the result is null.

            lower(string)

            string is the dimension column 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 column values, convert them to lowercase.

            lower([Account.Industry])
            ltrim Removes the specified substring or leading spaces from the beginning of a string.

            ltrim(string,[substring]

            string is the dimension column or text string to remove the leading spaces or specified substring from.

            substring is the value to remove from the string. This parameter is optional. If you don’t specify a substring, the transformation removes the leading spaces.

            Example: Trim the leading space characters from a string.

            ltrim('     5 Spaces Before')
            replace Replaces a substring with the specified characters. If any of the arguments are null, then the function returns null. This function is case-sensitive.

            replace(string,searchString,replacementString)

            string is the dimension column 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 or trailing spaces from the end of a string.

            rtrim(string,[substring]

            string is the dimension column or text string to remove the trailing spaces or specified substring from.

            substring is the value to remove from the string. This parameter is optional. If you don’t specify a substring, the transformation removes the trailing spaces.

            Example: Trim the 2 trailing space characters from a string.

            rtrim('2 Spaces After  ')
            substr Returns characters from the string, starting at the specified position and of the specified length.

            substr(string,position,[length])

            string is the dimension column 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.

            length is the number of characters to return. If length is 0, the output is an empty string. If length 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(string)

            string is the dimension column 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 (or leading and trailing spaces) from the string.

            Use the trim function in a SAQL query to trim other types of characters.

            trim(string,[substring])

            string is the dimension column or text string to remove the specified substring from.

            substring is the value removed from the string. This parameter is optional. If you don’t specify a substring, 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(string)

            string is the dimension column 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])
             
            Loading
            Salesforce Help | Article