String Functions

Version:
Current
Last modified: September 23, 2020

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.

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.

The CONTAINS function is case insensitive while the FINDSTRING function is case sensitive.

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 narrow. This function takes a Unicode string and translates it to a lowercase, narrow character string. All accents and other decorations are removed.

This function is useful for matching only. It is not considered a normalized string.

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.

The EndsWith function is case insensitive while the FindString function is case sensitive.

FindString

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.

Example

FindString([Name], "John") returns 0 when the string contains 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.

GetWord

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.

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

Example

Length("92688") returns a value of 5.

LowerCase

LowerCase(String): Converts a string to lower case.

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 turned into ? before calculating the MD5 hash.

MD5_UNICODE

MD5_UNICODE(String): For both original engine and AMP, calculates the MD5 hash of the string stored as UTF-16.

MD5_UTF8

MD5_UNICODE(String): Calculates the MD5 hash of the string stored as UTF-8.

Example

Md5_Ascii(‘Lá’), stored as Latin1, it takes two bytes, 4C E1. The function computes the MD5 of the bytes, giving the result "0c0ee86cc87d87125ad8923562be952e".

Md5_Unicode(‘Lá’), stored as UTF-16, it takes four bytes, 4C 00 E1 00. The function computes the MD5 of the bytes, giving the result "aa9969dfcca04249842cc457e5b3dd01".

Md5_Utf8(‘Lá’), stored as UTF-8, it takes three bytes, 4C C3 A1. The function computes the MD5 of the bytes, giving the result "68f00289dc3be140b1dfd4e031d733f1".

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.

Consult the Boost Regex Perl Regular Expression Syntax page to make the building of the expression easier.

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 '.*'.

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.

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.

Consult the Boost Regex Perl Regular Expression Syntax page to make the building of the expression easier. The replace parameter can be either a specified value as shown below or a marked group, such as "$1".

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_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.

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 judgement 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.

The StartsWith function is case insensitive while the FindString function is case sensitive.

Example

StartsWith('ABC123', 'ABC') returns True.

StartsWith('ABC123', 'abc') returns True.

StartsWith('ABC123', 'abc', 0) returns True.

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.

Example

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

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.

Was This Helpful?

Running into problems or issues with your Alteryx product? Visit the Alteryx Community or contact support.