Character functions

Use a character function to manipulate character strings in a formula. The input is a column of your dataset, and functions are applied to cell contents.

Table 1: Character functions

Function

Syntax

Description

<matchExpr> like <pattern>

  • matchExpr: The string expression to search
  • pattern: The pattern string constant to search for

Determines whether a character string matches a specified pattern. The search is not case-sensitive.

The pattern can include regular characters and the following special characters:
  • "_" matches a single character
  • "%" matches zero to many characters
Before you can use a special character as a regular character, you must escape it, using a backslash ("\").
Note "[", "^", "-", and "]" are reserved for future use.
For example:
"Hiking is fun" like "H% is _un"
returns true

Concatenate(str1, str2)

  • str1: First string
  • str2: Second string

Concatenates two strings into a single string.

The operator + can also concatenate strings.

For example:
Concatenate("Mr", "Brown")
returns "MrBrown"

Contain(whereStr, whatStr)

  • whereStr: String in which a search is conducted
  • whatStr: Substring that is the object of the search

Returns occurrences of a string within another string. The search is not case-sensitive.

For example:
Contain("Cats are grey", "aRe")
returns true

ExceptFirstWord(str, sep)

  • str: Input string
  • sep: A separator

Returns a copy of a string, with the first word removed.

For example:
ExceptFirstWord("Level 3, Standford Street", ", ")
returns "Standford Street"

ExceptLastWord(str, sep)

  • str: Input string
  • sep: A separator

Returns a copy of a string, with the last word removed.

For example:
ExceptLastWord("james.brown@company.com", "@")
returns "james.brown"

FirstWord(str, sep)

  • str: Input string
  • sep: A separator

Returns the first word of a string.

For example:
FirstWord("Senior Developer", " ")
returns "Senior"

LastWord(str, sep)

  • str: Input string
  • sep: A separator

Returns the last word of a string.

For example:
LastWord("Red/Purple", "/")
returns "Purple"

Length(str)

str: Input string

Returns the length of a string.

For example:
Length("How long")
returns 8

LowerCase(str)

str: Input string

Returns a copy of a string, with all characters converted to lowercase.

For example:
LowerCase("GOOD JOB")
returns "good job"

Lpad(str, length, pad)

  • str: Input string
  • length: Expected length
  • pad: Character sequence to add

Returns a copy of a string, padded with leading characters to the specified total length.

For example:
Lpad("Incomplete field", 20, "#")
returns "####Incomplete field"

Replace(str, target, replacement)

  • str: Input string
  • target: String to be replaced
  • replacement: String value to insert

Returns a string, with all occurrences of a specified string replaced with another specified string.

For example:
Replace("hyperthermia", "ert", "ot")
returns "hypothermia"

Rpad(str, length, pad)

  • str: Input string
  • length: Expected length
  • pad: Character sequence to add

Returns a copy of a string, padded with trailing characters to the specified total length.

For example:
Rpad("Incomplete field", 20, "#")
returns "Incomplete field####"

SubString(str, start)

  • str: String from which a substring is computed
  • start: Start position in the input substring

Returns a substring of a string.

For example:
SubString("Wong", 3)
returns "ng"

SubString(str, start, length)

  • str: String from which a substring is computed
  • start: Start position in the input substring
  • length: Length of the substring to return

Returns a substring of a string.

For example:
SubString("Wong", 2, 2)
returns "on"

ToText(param)

param: Parameter to convert

Converts a parameter to a string. All parameters are valid, and numbers are truncated to zero decimal places.

Trim(str, toTrim)

  • str: Input string
  • toTrim: Character to be removed

Returns a copy of the string, with the leading and trailing repetitions of a character removed. This function is case-sensitive.

For example:
Trim("Aurora", "a")
returns "Auror"

TrimLeft(str, toTrim)

  • str: Input string
  • toTrim: Character to remove

Returns a copy of the string, with the leading occurrence of a character removed. This function is case-sensitive.

For example:
TrimLeft("Above", "A")
returns "bove"

TrimRight(str, toTrim)

  • str: Input string
  • toTrim: Character to be removed

Returns a copy of a string, with trailing repetitions of a character removed. This function is case-sensitive.

For example:
TrimRight("Laura", "a")
returns "Laur"

UpperCase(str)

str: Input string

Returns a copy of a string, with all characters converted to uppercase.

For example:
UpperCase("Little Boy")
returns "LITTLE BOY"

ToDate(string, format)

The date format is a combination of the following reserved tokens, separated by delimiters:
  • d or dd: Day of month (1-31)
  • M or MM: Month of year (1-12)
  • y or yy: Abbreviated year without century (00-99)

    yyyy: Year with century (1956, 2012, 2014, and so on)

All other sequences are considered delimiters.
  • string: Input string to convert
  • format: Date format string constant

Converts an input string in a dataset to a date in a specified format, when the dates in a column of an original data source are in string format.

For example:
ToDate(Obj, 'yyyy/dd/MM')
converts a string in the format yyyy/dd/MM to a date
Table 2: Example of the Trim(str, toTrim) function: Trim ({Name},"a")

Name

Trimmed string

Aurora

Auror

Auror

Auror

auror

uror

aurora

uror

uror

uror

This formula returns "Auror": Trim("Aurora", "a").