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 

Returns the current date and time.

An alias for now().

today ()

text(today (),'dd/mm/yyyy')

text(today (),'HH:mm')