String Functions

These functions are available for Calculated Fields only.

Function LTrim( <String Expression> , <Expression> )
Description Returns a string value.
Arguments The string element can either be a field or a static string value. This is the string that will be trimmed. The expression element specifies how many characters of the string expression are to be trimmed (removed) from the left side of the string expression. The expression element will only accept a numeric value.
Examples

Status = underway

  • LTrim( Status , 5 ) would return way

Note: Not to be confused with SQL Server TRIM function that has different behaviour.

Function RTrim( <String Expression> , <Expression> )
Description Returns a string value.
Arguments The string element can either be a field or a static string value. This is the string that will be trimmed. The expression element specifies how many characters of the string expression are to be trimmed (removed) from the right side of the string expression. The expression element will only accept a numeric value.
Examples

Status = underway

  • RTrim( Status , 3 ) would return under

Note: Not to be confused with SQL Server TRIM functions that have different behaviours.

Function IndexOf( <String Expression> , <Expression> , <Optional Start Point> )
Description Returns the position (numeric) of a specified character in a string expression.
Arguments

The string element can either be a field or a static string value. This is the string that will be parsed to look for an expression. The expression element specifies the character/string/value that is to be searched for. The expression element will accept a numeric value, a single string character or a strong expression.

There is also an optional expression for the start point of the search (in positional terms) e.g. Search for the first ‘&’ symbol that appears after the first ‘$’ symbol.
Examples

Phrase = abc:123&456:Oscar:test

  • IndexOf( Phrase , ‘:’ ) would return a value of 3 (a numeric value)
  • IndexOf( Phrase , ‘a’ ) would return a value of 0
  • IndexOf( Phrase , ‘:’, 5 ) would return a value of 11
  • IndexOf( Phrase , ‘&’, 10 ) would return a value of -1
  • IndexOf( Phrase , ‘:’, IndexOf(Phrase, ‘&’) ) would return a value of 11

Note: The first position is 0 (zero). If the expression being searched for is not found, -1 will be returned.

Function LastIndexOf( <String Expression> , <Expression> , <Optional Start Point> )
Description Returns the last position (numeric) of a specified character in a string expression.
Arguments

The string element can either be a field or a static string value. This is the string that will be parsed to look for an expression. The expression element specifies the character/string/value that is to be searched for. The expression element will accept a numeric value, a single string character or a strong expression.

There is also an optional expression for the start point of the search (in positional terms) e.g. Search for the first ‘&’ symbol that appears after the first ‘$’ symbol.
Examples

Phrase = abc:123&456:Oscar:test

  • LastIndexOf( Phrase , ‘:’ ) would return a value of 17 (a numeric value)
  • LastIndexOf( Phrase , ‘a’ ) would return a value of 15
  • LastIndexOf( Phrase , ‘:’, 5 ) would return a value of 11
  • LastIndexOf( Phrase , ‘&’, 15 ) would return a value of -1
  • LastIndexOf( Phrase , ‘:’, LastIndexOf(Phrase, ‘&’) ) would return a value of 3

Note: The first position is 0 (zero). If the expression being searched for is not found, -1 will be returned.

Function SubString( <String Expression> , <Expression> , <Expression> )
Description Returns the specified part of a string expression.
Arguments

The string expression can be a field or a static string value. This can be a date, a string or a numeric value. The first expression is the start position of the substring. The second expression is the end position of the substring.

The second expression is optional. Leaving this blank will return the remainder of the string expression after the specified start point.
Examples

Phrase = price:3.99:cost:2.99

  • SubString(Phrase, 6) would return 3.99:cost:2.99
  • SubString(Phrase, 0, 5) would return price
  • In combination with IndexOf() the following can be used:
  • SubString(Phrase, 0, IndexOf(Phrase, ‘:’)) will return price
  • SubString(Phrase, LastIndexOf(Phrase, ‘:’)) will return 2.99
  • SubString(Phrase, IndexOf(Phrase, ‘:’)+1,IndexOf(Phrase,’:’,IndexOf(Phrase,’:’)+1)) will return 3.99

Note: The first position is 0 (zero).

Function RTrimSpaces( <String Expression> )
Description Removes space padding to the right of a string
Arguments The string element can either be a field or a static string value. This is the string that will be trimmed.
Examples

Value = “Hat “

  • Would return “Hat”
Function LTrimSpaces( <String Expression> )
Description Removes space padding to the left of a string
Arguments The string element can either be a field or a static string value. This is the string that will be trimmed.
Examples

Value = “ Hat“

  • Would return “Hat”
Function ToLower( <String Expression> )
Description Returns the provided expression in lower case
Arguments The string element can either be a field or a static string value.
Examples

Value = “Hat“

  • Would return “hat”
Function ToUpper( <String Expression> )
Description Returns the provided expression in upper case
Arguments The string element can either be a field or a static string value.
Examples

Value = “Hat“

  • Would return “HAT”
Function Replace( <String Expression> , <String Expression> , <String Expression> )
Description Replaces part or all of a string with another string
Arguments The string expression can be a field or a static string value. This can be a date, a string or a numeric value.
Examples
  • Replace(Area_Name, ‘Northern England and Ireland’, ‘NE & I’) Expression = Northern England and Ireland Would return NE & I
Function IsFormat( <String Expression>,<Pattern>)
Description Returns ‘True’ or ‘False’ on whether a field/string is in a specified format
Arguments The string expression can be a field or a static string value. This can be a date, a string or a numeric value.
Examples

IF IsFormat(Transaction_Date_Time, ‘dd/MM/yyyy hh:mm:ss’ THEN {1} AND {0}

Expression: 10/05/2016 15:10:55

  • Would return 1
Function Format( <Expression> , <Pattern> )
Description Changes the format of a field or string
Arguments The string expression can be a field or a static string value. This can be a date, a string or a numeric value.
Examples

Format(Transaction_Date_Time, ‘dd/MM/yyyy hh:mm:ss’)

Expression : 01/01/2010

  • Would return 01/01/2010 00:00:00