String Functions
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. Use string functions with String data types only.
Contains
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.
Example
Contains('123ABC', 'ABC') returns True.
Contains('123ABC', 'abc') returns True.
Contains('123ABC', 'abc', 0) returns False.
CountWords
CountWords(string): Returns the count of words in the specified string. Words are defined by characters separated by a space.
Example
CountWords("Basic Variables Households") returns 3.
CountWords("Basic Variables Age:Female (Pop)Age 1") returns 5.
DecomposeUnicodeForMatch
DecomposeUnicodeForMatch(String): Removes accents and expands compound characters while converting to a narrow string. This function takes an Alteryx WString data type and converts it to a lowercase, narrow String data type. All accents and other decorations are removed. Refer to Data Types for more information about Alteryx data types.
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 '?'.
Example
DecomposeUnicodeForMatch("Prénoms français") returns prenoms francais.
EndsWith
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.
Example
EndsWith('123ABC', 'ABC') returns True.
EndsWith('123ABC', 'abc') returns True.
EndsWith('123ABC', 'abc', 0) returns False.
FindString
Important
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). If there is no occurrence, it returns -1.
Example
FindString([Name], "John") returns the 0-indexed position of the first character of the target string if the provided string contains the target and returns -1 when the string does not.
FindString("John Smith", "John")returns 0. The first character of the target string ("J") is in the 0 position or "John Smith."FindString("Michael John Smith", "John")returns 8. The first character of the target string ("J") is in the 8 position (0-based index) of "Michael John Smith."FindString("David Smith", "John")returns -1. The first character of the target string ("J") is not found in string "David Smith."
GetWord
GetWord(string, n): Returns the Nth (0-based) word in the string (0-based index means the first word is at the 0 position, the second is at the 1 position, and so on). Words are defined as a collection of characters separated by a space, tab, return, or newline character. Multiple delimiters in a row are treated as a single delimiter.
Example
GetWord("Basic Variables Households", 0) returns "Basic".
GetWord("Basic Variables Households", 1) returns "Variables".
Left
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.
Example
Left("92688", 3) returns a value of "926".
Length
Length(String): Returns the length of the string (String).
Warning
Length results might differ between this Designer function and SQL functions. This depends on how any particular database handles wide characters and line breaks. For example, newline characters aren't counted in Length in PostgreSQL while they are counted in the Designer Length function, and results in Designer may differ depending on whether the ANSI or Unicode driver is used.
Example
Length("92688") returns a value of 5.
LowerCase
LowerCase(String): Converts a string to lowercase.
Example
LowerCase("M1P 1G6") returns "m1p 1g6".
MD5_ASCII
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, not recommended for WString types. You should avoid using this function on strings that might contain wide characters.
MD5_UNICODE
MD5_UNICODE(String): For both the original engine and AMP, calculates the MD5 hash of the string stored as UTF-16.
MD5_UTF8
MD5_UTF8(String): Calculates the MD5 hash of the string stored as UTF-8.
Example
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
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 one character long, only the first character is used.
Example
PadLeft("M", 4, "x") returns "xxxM".
PadRight
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 one character long, only the first character is used.
Example
PadRight("M", 4, "x") returns "Mxxx".
REGEX_CountMatches
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
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 '.*'.
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.
Example
REGEX_Match("123-45-6789", "\d{3}-\d{2}-\d{4}") returns -1 (True).
REGEX_Replace
REGEX_Replace(String, pattern, replace, icase): Allows replacement of text using regular expressions and returns the string resulting from the RegEx find (pattern) and replace (string). All occurrences of the match are replaced, not just the first.
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.
Example
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
Replace(String, Target, Replacement): Returns the string (String) after replacing each occurrence of the String (Target) with the String (Replacement).
Example
Replace("Good judgment comes from experience", "experience", "awareness") returns "Good judgment comes from awareness"
ReplaceChar
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 one character, only the first character is used. If (z) is empty, each character (String) that matches any character in (y) is simply removed.
Example
ReplaceChar("abcdefb", "b", "_") returns "a_cdef_".
ReplaceChar("@a#b%c", "@,#,%", "_") returns "_a_b_c".
ReplaceFirst
ReplaceFirst(String, Target, Replacement): Returns the string (String) after replacing the first occurrence of the string (Target) with the string (Replacement).
Example
ReplaceFirst("abcdefb", "b", "_") returns "a_cdefb".
ReverseString
ReverseString(String): Reverses all the characters in the string.
Example
ReverseString("abcdefb") returns "bfedcba".
Right
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.
Example
Right("92688", 3) returns a value of "688".
StartsWith
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.
Example
StartsWith('ABC123', 'ABC') returns True.
StartsWith('ABC123', 'abc') returns True.
StartsWith('ABC123', 'abc', 0) returns False.
STRCSPN
STRCSPN(String, y): Returns the length of the initial segment of the string (String) consisting of characters not in the string (y).
Example
STRCSPN("Bob's Amaco", "~!@#$%^&*'()") returns 3. This is a useful test to make sure there is no punctuation in the string.
StripQuotes
StripQuotes(String): Removes a matched set of quotation marks or apostrophes from the ends of the string.
Example
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
STRSPN(String, y): Returns the length of the initial segment of the string (String) consisting of characters in the string (y).
Example
STRSPN("3034408896x105", "0123456789") returns 10. This is a useful test to make sure a string consists of a set of characters.
Substring
Substring(String, start, length): Returns the substring of (String) starting at (start), stopping after (length), if provided.
Example
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
TitleCase(String): Converts a string to title case.
Example
TitleCase("john smith") returns "John Smith".
Trim
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.
Example
Trim("!see instructions!!!", "!") returns "see instructions".
Trim(" Test123 ") returns "Test123".
TrimLeft
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.
Example
TrimLeft("** special invitation ", " *") returns "special invitation ".
TrimRight
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.
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.
Example
TrimRight("John Smith ") returns "John Smith".
TrimRight("John Smith**","*") returns "John Smith".
Uppercase
Uppercase(String): Converts a string to upper case.
Example
Uppercase("John Smith") returns "JOHN SMITH".
Uuid
UuidCreate(): Creates a unique identifier.
Example
UuidCreate() returns a unique value such as ba131836-1ba3-4d42-8f7e-b81a99c2e838.
About Length and Strings
When referencing the length of a string, the first character is counted as 1 (the length of the string "record" is 6).
However, when referencing the character position within a string, positions are actually counted between characters. So think of the first character's position as 0. Therefore, the position of the letter "c" in the string "record" is at position 2.