I would like to explain the most frequently used string functions in Alteryx. Before going to the string function let us first know about what is a string function?

String function:

A string function performs operations on text data. We can 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.

 

1.Contains([string], Target):

Contains searches for the occurrence of a particular string within a string.  It Returns a TRUE or FALSE if the string matches the pattern.

[string]- Is the input string.

[Target]-Is the particular string you are searching for.

Let us take an Example:

This my Input string: The Data school Australia.

Now, I want to search for Australia.

So, we can use contains function

Contains ([Input string],” Australia”)

It will return true as in the above input we have Australia.

 

2.Get Word (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.

String-Is the input string.

n- Is the length word you are searching for.

Example:

Input string: The Data school Australia.

Now, I want to get a word Data from the input string.

So, now in n we need to give 1 because as each word starts from zero (0) and the space is not counted.

Get word ([Input string],1)

It will return  Data.

 

3.Left (x, Len):

Returns the first [Len] characters of the string (x). If Len is less than 0 or greater than the length of x, x remains unchanged.

x- Is the Input string.

Len- Is the length of the character you want to get.

Example:

Input string: January-01-2018.

Now, I want only month from the date input string.

Left ([Input string],7)

It will return January.

 

4.Right (x, 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.

x- Is the Input string.

Len- Is the length of the character you want to get.

Example:

Input string: January-01-2018.

Now, I want only the year from the date input string.

Right ([Input string],4)

It will return 2018.

 

5.Substring (x, start, length):

Returns the substring of (x) starting at (start), stopping after(length).

x- Is the input string.

Start- Is an integer that specifies where the returned characters start.

Length- Is a integer

expression that specifies how many characters of the expression will be returned.

Example:

Input string: The Data School Australia.

Now, I want to return only Data school from the above input string.

So, start is 4

Length is 12.

Substring ([Input string],4,12).

It will return Data School.

 

6. Length(x):

It will Returns the length of the string (x).

x- Is the input string.

Example:

Input string: The Data School Australia.

It will return 26.

 

7.Pad Left (str, 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 will be used.

Str-Is the input string.

Len-It is the length of characters you want to add.

Char- It is the char that you want to add in your input.

Example:

In a column named as date we have 2 dates

Input string:

Day

1
23

 

Now, I want to add 0 to the day in row one.

Pad left([Date],2,0)

 

It will return:

  Day

01
23

 

 

8.Pad Right (str, 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 will be used.

Str-Is the input string.

Len-It is the length of characters you want to add.

Char- It is the char that you want to add in your input.

Example:

In a column named as date we have 2 dates

Input string:

   Day

1
23

 

Now, I want to add 0 to the day in row one.

Pad left([Date],2,0)

 

It will return:

Day

10
23

 

 

9.Find String (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

Str It is an Input string.

Target- The string you are searching

Example:

Input Data: The Data School Australia

Now, I want to search School from the above Input data.

FindString([Input Data],”School”)

It will return 9

 

 

10.Replace (Str, Target, Replacement):

It will Replaces all occurrences of a specified string value with another string value

Str It is an Input string.

Target- The string you are searching for to replace with Replacement string.

Example:

Input string: The Data School Australia.

Now, I want to replace Australia with UK.

Replace ([Input string], Australia,”UK”)

It will return:

The Data School UK.