Function
Description
Example
concatenate
Adds text strings together. Any number of arguments can be specified when multiple strings need to be concatenated.
concatenate(value,value)
concatenatewith
Adds any string of values together (can be any kind of value). Any number of arguments can be specified when multiple values need to be joined together.
concatenatewith(<delimitr,value1,value2,etc)
trim
Removes leading and trailing whitespace from a string.
trim(value)
if
Evaluates the condition and returns a value depending on its result.
if(expression, True value, False value)
text
Converts any value to its string representation. Optional arguments can be used to specify the format.
Version 1.26.0.X and higher versions of HoleBASE now support the processing of null values.
text(value)
text(value, format)
value
Attempts to convert a given value to its numeric equivalent. This is useful when a function requires a numeric typed argument and only a string is available.
value(string)
mid
Extracts a substring at a given position within a string. The position and length cannot exceed the number of characters which the string has. Position is 0-indexed.
mid(string, start position, length)
left
Extracts the substring from the beginning of a string with a specific length.
left(string, length)
right
Extracts the substring from the end of a string with a specific length.
right(string, length)
find
Locates the position of a substring within a string. The initial search position begins the search at a specific index.
find(substring, string, start position)
search
Locates the position of a substring within a string. The initial search position begins the search at a specific index. This is an alias for find().
search(substring, string, start position)
replace
Replaces all occurrences of a substring with an alternate value.
replace(string, old, new)
substitute
Replaces all occurrences of a substring with an alternate value. This is an alias for replace()
substitute(string, old, new)
chr
Returns the character for an ASCII value. For example 97 returns 'a'.
chr(97)
not
Inverts the Boolean truth of an argument. If the argument is not a Boolean, false is returned.
not(True)
and
Returns the truth of applying the logical AND operator on Boolean arguments.
or
Returns the truth of applying the logical OR operator on Boolean arguments.
if('a'='a' or 'b'='b','Yes','No')
exact
Returns True if two string arguments are identical. Otherwise, False is returned.
exact(string, string)
fixed
Returns a numeric value to a fixed number of decimal places. An option exists to include commas if required.
fixed(value, decimal places, false)
len
Determines the length of a string.
len(string)
isblank
Determines whether a parameter is blank / empty or null. This checks whether a cell has whether a cell has no value or is empty.
isblank(value)
isnumber
Determines if a value is numeric.
Version 1.26.0.X and higher versions of HoleBASE now support the processing of null values as False.
isnumber(value)
isnull
Determines if a value is 'null'. A null value indicates the absence of a value.
isnull(value)
numberordefault
Returns a specified value if it does not equal null. If the value is null, a default value is returned instead.
numberordefault([SPT.NValue],0)
now
Returns the current date and time.
now()
text(now(),'dd/MM/yyyy')
text(now(),'HH:mm')
today
An alias for now().
today ()
text(today (),'dd/mm/yyyy')
text(today (),'HH:mm')