In Designer Cloud, a string function performs operations on text data. Use a string function to cleanse data, convert data to a different format or case, compute metrics about the data, or perform other manipulations. A string function can only be used with String data types.
Note
Not all functions are supported between Standard mode and Cloud Native mode tools. For a list of supported functions, go to the respective Standard mode and Could Native mode function lists found on the Formula tool page.
Contains(String, Target, CaseInsensitive=1)
: Searches for the occurrence of a particular string within a string. Returns True if (String) contains (Target), else returns False.
Contains('123ABC', 'ABC')
returns True.
Contains('123ABC', 'abc')
returns True.
Contains('123ABC', 'abc', 0)
returns False.
CountWords(string)
: Returns the count of words in the specified string. Words are defined by characters separated by a space.
CountWords("Basic Variables Households")
returns 3.
CountWords("Basic Variables Age:Female (Pop)Age 1")
returns 5.
DecomposeUnicodeForMatch(String)
: Removes accents and expands compound characters while converting to a narrow string. All accents and other decorations are removed.
Important
This function is useful for matching only. It is not considered a normalized string.
The function is not designed for use with non-western character sets like Japanese. You should avoid using this function on strings that might contain wide characters and note that wide characters are converted to '?'.
DecomposeUnicodeForMatch("Prénoms français")
returns prenoms francais.
EndsWith(String, Target, CaseInsensitive=1)
: Checks if a string ends with a particular string. Returns True if (String) ends with (Target), else returns False. It defaults to case insensitive.
EndsWith('123ABC', 'ABC')
returns True.
EndsWith('123ABC', 'abc')
returns True.
EndsWith('123ABC', 'abc', 0)
returns False.
FindNth(Initial String, Target, Instance)
: Finds the instance (nth occurrence) of a target string in the initial string and returns the 0-indexed position of the instance. The function is case-sensitive and doesn’t accept negative parameters.
FindNth("Hello World","o",2)
returns 7. The function finds the 2nd instance of "o" in the provided string and returns the 0-indexed position, which in this case is 7.
FindNth("Hello World","World",1)
returns 6. The function finds the 1st instance of "World" in the provided string and returns the 0-indexed position of the first character, which in this case is 6.
FindNth("Hello World","o",1.8)
returns 7. The function finds the 2nd instance (rounds 1.8 to the nearest integer) of "o" in the provided string and returns the 0-indexed position of the 2nd character, which in this case is 7.
FindNth("Hello World","world",1)
returns -1. The function finds the 1st instance of "world" in the provided string and returns the 0-indexed position of the first character, which in this case is -1 (match not found due to case sensitivity).
FindNth("Hello World","l",0)
returns -1. The function finds the 0th instance of "l" in the provided string and returns the 0-indexed position, which in this case is -1 (not found because the 0th position doesn't exist).
FindNth("Hello World","l",-1)
returns -1. Negative parameters are not applicable, so the function returns -1 (not found).
FindNth("Hello World", Null(), 1)
returns -1. Null and empty are not found.
FindNth(Hello world, "l", 1)
returns an error. The function requires that the first 2 parameters are strings.
The FindString
function is case sensitive.
FindString(String,Target)
: Searches for the occurrence of a particular string (Target) within another string (String) and returns the numeric position of its occurrence in the string.
Returns the 0-based index of the first occurrence of (Target) in (String). Returns -1 if no occurrence.
FindString([Name], "John")
returns 0 when the string starts with John and returns -1 when the string does not.
IF (FINDSTRING([Name], "John") =0) THEN "John Smith" ELSE "Other" ENDIF
returns John Smith when the string contains John and returns Other when the string does not.
GetLeft(String, Delimiter)
: Returns the left part of the provided (String) until the first instance of the specified 1 or more (Delimiter).
Note
The Delimiter
operator is case-sensitive. If you specify "a" as the delimiter, the function only uses "a" as the delimiter, not "A".
GetLeft("Automated Analytics for All", " ")
returns "Automated".
GetLeft("Enable fast, confident decisions across the enterprise.", ",")
returns "Enable fast".
GetPart(String, Delimiter, Index)
: Uses the specified 1 or more (Delimiters) to divide (String) into substrings, and returns the substring in the position that is specified in (Index).
This function uses a 0-based index. For example, if Index is 2, the function returns the string from the position in index 2, which is the third substring.
Note
The Delimiter
operator is case-sensitive. If you specify "a" as the delimiter, the function only uses "a" as the delimiter, not "A".
GetPart("Automated Analytics for All", " ", 1)
returns "Analytics".
GetPart("Automated Analytics for All", " ", 0)
returns "Automated".
GetPart("Enable fast, confident decisions across the enterprise.", ", ", 5)
returns "across". Note that this example uses 2 delimiters—a comma and a space. The function splits the string when it encounters either delimiter.
GetPart("January 1, 2023", " ", 2)
returns "2023".
GetRight(String, Delimiter)
: Returns the right part of the provided (String) after the first instance of the specified 1 or more (Delimiters).
Note
The Delimiter
operator is case-sensitive. If you specify "a" as the delimiter, the function only uses "a" as the delimiter, not "A".
GetRight("Automated Analytics for All", " ")
returns "Analytics for All".
GetRight("Enable fast, confident decisions across the enterprise.", ",")
returns " confident decisions across the enterprise." (note the space beforeconfidentin the return).
GetWord(string, n)
: Returns the Nth (0-based) word in the string. Words are defined as a collection of characters separated by a space. 0-based index, means the first word is at the 0 position.
GetWord("Basic Variables Households", 0)
returns "Basic".
GetWord("Basic Variables Households", 1)
returns "Variables".
Left(String, len)
: Returns the first (len) characters of the string (String). If len is less than 0 or greater than the length of String, String remains unchanged.
Left("92688", 3)
returns a value of "926".
Length(String)
: Returns the length of the string (String).
Length("92688")
returns a value of 5.
LowerCase(String)
: Converts a string to lower case.
LowerCase("M1P 1G6")
returns "m1p 1g6".
MD5_ASCII(String)
: Calculates the MD5 hash of the string. The string is expected to be only narrow characters. Wide characters are converted to '?' before computing the hash. Use for String types. You should avoid using this function on strings that might contain wide characters.
MD5_UNICODE(String)
: Calculates the MD5 hash of the string stored as UTF-16.
MD5_UTF8(String)
: Calculates the MD5 hash of the string stored as UTF-8.
Md5_Ascii("Lá")
, stored as Latin1, it takes 2 bytes, 4C E1. The function computes the MD5 of the bytes, giving the result "0c0ee86cc87d87125ad8923562be952e".
Md5_Ascii("Lá ☢")
, the ☢ character, being wide, is replaced with a '?', so it's as if you were computing Md5_Ascii("Lá?"). That is stored as Latin1 in 3 bytes, 4C E1 3F. The function computes the MD5 of the bytes, giving the result "a5a308ab19acf900efea8fc7b5b77b4d".
Md5_Unicode("Lá")
, stored as UTF-16, it takes 4 bytes, 4C 00 E1 00. The function computes the MD5 of the bytes, giving the result "aa9969dfcca04249842cc457e5b3dd01".
Md5_Unicode("Lá ☢")
, when stored as UTF-16 takes 8 bytes, 4C 00 E1 00 3C D8 C8 DF. The function computes the MD5 of the bytes, giving the result: "7c4762d93572dd02a8a405232e966b18".
Md5_Utf8("Lá")
, stored as UTF-8, it takes 3 bytes, 4C C3 A1. The function computes the MD5 of the bytes, giving the result "68f00289dc3be140b1dfd4e031d733f1".
Md5_Utf8("Lá ☢")
, when stored as UTF-8 takes 7 bytes, 4C C3 A1 F0 9F 8F 88. The function computes the MD5 of the bytes, giving the result "383fc0355db728a2078ce41a2ab6211b".
PadLeft(String, len, char)
: Pads the string to the left with the specified character to the specified length. If the padding (char) is more than 1 character long, only the first character is used.
PadLeft("M", 4, "x")
returns "xxxM".
PadRight(String, len, char)
: Pads the string to the right with the specified character to the specified length. If the padding (char) is more than 1 character long, only the first character is used.
PadRight("M", 4, "x")
returns "Mxxx".
REGEX_CountMatches(String,pattern,icase)
: Returns the count of matches within the string to the pattern.
icase
is an optional parameter. When specified, the case must match. By default icase=1 (meaning ignore case). If set to 0, the case must match.
REGEX_Match(String,pattern,icase)
: Searches a string for an occurrence of a regular expression.
Tells if the string matches the pattern from the first character to the end.
To look for something that does not necessarily start at the beginning of the string, start the pattern with '.*'.
To look for something that does not necessarily go all the way to the end of the string, end the pattern with '.*'.
Consult the Boost Regex Perl Regular Expression Syntax page for more information on how to properly construct a regular expression.
icase
is an optional parameter. When specified, the case must match. By default icase=1 meaning ignore case. If set to 0, the case must match.
REGEX_Match("123-45-6789", "\d{3}-\d{2}-\d{4}")
returns -1 (True).
REGEX_Replace(String, pattern, replace, icase)
: Returns the string resulting from the RegEx find pattern and replace string.
The replace parameter can be either a specified value as shown below or a marked group, like "$1". Make sure that marked groups are wrapped in double quotes.
icase
is an optional parameter. When specified, the case must match.
By default icase=1, which means ignore case.
If set to 0, the case must match.
REGEX_Replace("Don't reveal your Social Security number, 123-45-6789","\d{3}-\d{2}-\d{4}", "CLASSIFIED")
returns Don't reveal your Social Security number, CLASSIFIED.
REGEX_Replace("Change all domain names from alteryx@Alteryx.com","@.*\.", "@extendthereach.")
returns Change all domain names from alteryx@extendthereach.com.
REGEX_Replace("25 test","(\d+)\s.*","$1")
returns 25.
Replace(String, Target, Replacement)
: Returns the string (String) after replacing each occurrence of the String (Target) with the String (Replacement).
Replace("Good judgment comes from experience", "experience", "awareness")
returns "Good judgement comes from awareness"
ReplaceChar(String, y, z)
: Returns the string (String) after replacing each occurrence of the character (y) with the character (z). If the replacement character (z) is a string with more than 1 character, only the first character is used. If (z) is empty, each character (String) that matches any character in (y) is simply removed.
ReplaceChar("abcdefb", "b", "_")
returns "a_cdef_".
ReplaceChar("@a#b%c", "@,#,%", "_")
returns "_a_b_c".
ReplaceFirst(String, Target, Replacement)
: Returns the string (String) after replacing the first occurrence of the string (Target) with the string (Replacement).
ReplaceFirst("abcdefb", "b", "_")
returns "a_cdefb".
ReverseString(String)
: Reverses all the characters in the string.
ReverseString("abcdefb")
returns "bfedcba".
Right(String, len)
: Returns the last (len) characters of the string. If len is less than 0 or greater than the length of String, the string remains unchanged.
Right("92688", 3)
returns a value of "688".
StartsWith(String, Target, CaseInsensitive=1)
: Checks if a string starts with a particular string. Returns True if String starts with a particular string Target, else returns False.
StartsWith('ABC123', 'ABC')
returns True.
StartsWith('ABC123', 'abc')
returns True.
StartsWith('ABC123', 'abc', 0)
returns False.
STRCSPN(String, y)
: Returns the length of the initial segment of the string (String) consisting of charactersnotin the string (y).
STRCSPN("Bob's Amaco", "~!@#$%^&*'()")
returns 3. This is a useful test to make sure there is no punctuation in the string.
StripQuotes(String)
: Removes a matched set of quotation marks or apostrophes from the ends of the string.
StripQuotes("Hello there")
returns Hello there.StripQuotes("'Hello there,' she said.")
returns 'Hello there,' she said.StripQuotes('"Hello there," she said.')
returns "Hello there," she said.
STRSPN(String, y)
: Returns the length of the initial segment of the string (String) consisting of characters in the string (y).
STRSPN("3034408896x105", "0123456789")
returns 10. This is a useful test to make sure a string consists of a set of characters.
Substring(String, start, length)
: Returns the substring of (String) starting at (start), stopping after (length), if provided.
Substring("949-222-4356", 4, 8)
returns "222-4356".
Substring("949-222-4356", 4, 6)
returns "222-43".
Substring("949-222-4356", 4)
returns "222-4356".
TitleCase(String)
: Converts a string to title case.
TitleCase("john smith")
returns "John Smith".
Trim(String, y)
: Removes the character(s) in the string (y) from the ends of the string (String). Y is optional and defaults to trimming white space. Notice in the TRIM function examples the specified characters are trimmed. It doesn't matter what order the characters are in.
Trim("!see instructions!!!", "!")
returns "see instructions".
Trim(" Test123 ")
returns "Test123".
TrimLeft(String, y)
: Removes character in the string (y) from the beginning of the string (String). Y is optional and defaults to trimming white space.
TrimLeft("** special invitation ", " *")
returns "special invitation ".
TrimRight(String, y)
: Removes character in the string (y) from the end of the string (String). Y is optional and defaults to trimming white space.
TrimRight("John Smith ")
returns "John Smith".
TrimRight("John Smith**","*")
returns "John Smith".
While you might be passing in a string of characters to trim, the TRIM functions do not respect the order of the characters; it treats the string as a "list." Therefore it will trim ALL of the characters in the list. If you are looking to replace a string, use a REPLACE function or a REGEX function in your expression.
Uppercase(String)
: Converts a string to upper case.
Uppercase("John Smith")
returns "JOHN SMITH".
UuidCreate()
: Creates a unique identifier.
UuidCreate()
returns a unique value such as ba131836-1ba3-4d42-8f7e-b81a99c2e838.