Skip to content

String Functions (StringUtils.xml)

James Dunkerley edited this page Mar 26, 2019 · 8 revisions

This provides a few extension methods for working with strings. All functions treat strings as Unicode so can used with all the string types.

As Alteryx represents Dates, Times and DateTimes as strings they can also be used on those types.

All functions will be added to the String category in the function list.

Unit tests for all these functions in StringUtils.Test folder.

FindStringLast

Syntax: FINDSTRINGLAST(Haystack, Needle)

Examples:

  • FINDSTRINGLAST('Hello World', 'World') returns 6
  • FINDSTRINGLAST('Hello World', NULL()) returns 0
  • FINDSTRINGLAST('This is some text', 'World') returns -1

Gets the index of the last Needle in the Haystack string. If not found will return -1.

  • Return index starts at 0.

LeftPart

Syntax: LEFTPART(Text, Delimiter)

Examples:

  • LEFTPART('Hello World', ' ') returns 'Hello'
  • LEFTPART('Hello', ' ') returns 'Hello'
  • LEFTPART('This is some text', ' is ') returns 'This'

Gets the part of Text before the Delimiter.

  • The Delimiter can be longer than a single character.
  • If Text is NULL, then the function will return NULL.
  • If Delimiter is NULL or an empty string, then the Text value will be returned.
  • If Delimiter is not found within the Text, then the result will be the Text value.

RightPart

Syntax: RIGHTPART(Text, Delimiter)

Examples:

  • RIGHTPART('Hello World', ' ') returns 'World'
  • RIGHTPART('Hello', ' ') returns 'Hello'
  • RIGHTPART('This is some text', ' is ') returns 'some text'

Gets the part of Text after the Delimiter.

  • The Delimiter can be longer than a single character.
  • If Text is NULL, then the function will return NULL.
  • If Delimiter is NULL or an empty string, then the Text value will be returned.
  • If Delimiter is not found within the Text, then the result will be the Text value.

Split

C++ Function - Split

Syntax: SPLIT(Text, Delimiter, Index)

Examples:

  • SPLIT('Hello World', ' ', 1) returns 'Hello'
  • SPLIT('Hello', ' ', 1) returns 'Hello'
  • SPLIT('Hello World', ' ', 3) returns NULL

Splits Text into an array and then returns the specified index (first element is index 1)

  • If the delimiter is more than one character, then each character is used as a delimiter.
  • There is no support for " blocks containing delimiters.
  • Repeated delimiters will result in an empty string value in the array.
  • If the index is greater than the array length, then the result is NULL.
  • If Text is NULL, then the function will return NULL.

Deprecated Functions

The following functions have been added to Alteryx in version 10. They are still contained within the StringUtils.xml but commented out. Should you wish to use these functions just uncomment the sections in the XML.

Contains

Syntax: CONTAINS(Text, Target)

Examples:

  • CONTAINS('Hello World', ' ') returns TRUE
  • CONTAINS('Hello World', ',') returns FALSE

Returns TRUE if Target is contained in Text.

  • If both Text and Target are NULL, returns true.
  • If Text is NULL and Target is not, then the function will return false.
  • If Target is NULL or an empty string, and Text is not, returns true.

StartsWith

Syntax: STARTSWITH(Text, Target)

Examples:

  • STARTSWITH('Hello World', 'Hello') returns TRUE
  • STARTSWITH('Hello World', 'World') returns FALSE

Returns TRUE if Text starts with Target.

  • If both Text and Target are NULL, returns true.
  • If Text is NULL and Target is not, then the function will return false.
  • If Target is NULL or an empty string, and Text is not, returns true.

EndsWith

Syntax: ENDSWITH(Text, Target)

Examples:

  • ENDSWITH('Hello World', 'World') returns TRUE
  • ENDSWITH('Hello World', 'Hello') returns FALSE

Returns TRUE if Text ends with Target.

  • If both Text and Target are NULL, returns true.
  • If Text is NULL and Target is not, then the function will return false.
  • If Target is NULL or an empty string, and Text is not, returns true.

ToRoman

Syntax: TOROMAN(Value)

Examples:

  • TOROMAN(10) returns X

Returns the Roman Numeral representation of a number. Valid inputs from 1 to 5000.

FromRoman

Syntax: FROMROMAN(RomanNumerals)

Examples:

  • FROMROMAN('X') returns 10

Returns numeric value of a the Roman Numeral. Only tested to 5000, but should cope with a longer list of Ms!

RandomString

Syntax: RANDOMSTRING()

Creates a random string from a template

  • RandomIPAddress: Creates a random IP address, can be contrained by a CIDR range
  • RandomItem: Picks a random item from the list of parameters
You can’t perform that action at this time.