Help and Support > Table of Contents > Function reference

Text Functions

CHAR(ascii character code)

Returns the ASCII character for the number provided.

CHAR(34)= "
CHAR(65)= A


CLEANCHARS(text to clean,all characters to replace,string to insert)

Removes or replaces characters within a string.

CLEANCHARS("Hello!","e!","a")= "Halloa"

To remove characters, set the string to insert to an empty string:

CLEANCHARS("Hello!","HL","")= "eo!"


CONTAINS(Text, Text to search for 1, Text to search for 2,...)

Returns TRUE if the string provided contains any of the strings specified in any position. Not case sensitive. To match more closely, use the IN function.

CONTAINS("This is the string to test","this","cat","dog")=TRUE


IN(Text to test, Text 1, Text 2, Text 3,...)

Returns TRUE if string matches one of the strings provided. Similar to CONTAINS but requires a match. Not case sensitive.

IN("test","cat","dog","bear")=FALSE
IN("test","cat","dog","bear","test")=TRUE
IN("dog","cat","dog","bear","test")=TRUE
IN("test this","cat","dog","bear","test")=FALSE


LEFT( Text, Number of char to include)

Returns the left most part of a string based on the number of characters requested. If the number of characters requested are not available, will return an error.

LEFT("ABCD",2)="AB"
LEFT("ABCD",5) results in error.


LEN (Text)

Returns the length of a text string or number including white space and punctuation.

LEN("this is a long one.")=19
LEN("ABC")=3
LEN(45.4)=4


LettersOnly(Text)

Returns a string that is the orginal string, with only spaces and letters remaining (a-z and A-Z) Any punctuation, or digits are removed

LETTERSONLY("ABC")="ABC"
LETTERSONLY("GOTCHA!")="GOTCHA"

LOWER(Text)

Returns the string in all lower case. Punctuation, numbers and white space are not affected.

LOWER("This string HAS case.")="this string has case."


LTRIM(Text,string to trim -if not included is space )

Trims specifed characters from the left of a string, if they exist- leaves the string alone if they do not exist. LTRIM is case sensitive.

LTRIM("This is a test","This")= " is a test"
LTRIM("Test this one.","this")="Test this one."
LTRIM("AB123@","@")="AB123@"
LTRIM("@AB123","@")="AB123"


MID( Text, Start Point, Number of char to include)

Extracts a part of a string based on the start location and the length of the string to be extracted. If the start location is not in the string, or if the length required is not present the function returns an error.

MID(""test"",1,2)=""te""
MID(""test"",1,5)=#(MID)#Index out of Range
MID(""This is a test."",6,2)=""is""


NumbersOnly(Text)

Returns a string with all non-digit characters stripped out.

NUMBERSONLY("$56.54")="56.54"
NUMBERSONLY("CUST2343")="2343"


REGEX(Text to text,Regex expression) (Professional edition only)

Evaluates a regular expression against a string. This function is very useful for testing values in a text column for valid formats. You can either develop your own regular expressions, or search for them (there are a number of expressions that people have created and share for validating all sorts of strings). For example, a regular expression that evaluates if a string appears to be valid email address is "^([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})$"

REGEX("test@test.com","^([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})$")=TRUE
REGEX("test.@test.com","^([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})$")=FALSE
REGEX("test.test.com","^([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})$")=FALSE


REPLACE(Text,start pos of text to remove,end pos of text to remove,text to insert)

Replaces a given string within a string value by specifying the start and length of the string segment to be removed, and the string to be inserted. The new string does not have to be the same size as the removed string.

replace("I like fruit.",8,5,"chocolate")="I like chocolate."


RIGHT( Text, Number of char to include)

Returns the right most part of a string, with a specified length. Right will return an error if it attempts to return more characters than exist in the string.

RIGHT("Digging",3)="ing"
RIGHT("Digging",20)=ERROR


RTRIM(Text,string to trim -if not included is space )

Trims specifed characters from the right of a string, if they exist- leaves the string alone if they do not exist.

RTRIM("Hey!","!")="Hey"
RTRIM("Hey!There","!")="Hey!There"


SPLIT(Text,delimiter,which field to use starting at 1)

Parses a string into multiple fields based on a specified delimiter, and returns the nth field, where the first field is 1. Returns an error if there are not enough fields.

SPLIT("this is the test"," ",1)="this"
SPLIT("this is the test"," ",3)="the"


SUBSTITUTE(Text, Text to remove, Text to replace, Optional Instance number to replace)

Substitutes a given string with another wherever it appears in a specified string.

substitute("Never play with fire.","play","run")="Never run with fire."
substitute("John was not interested. All John wanted to do was sleep.","John","Doug")="Doug was not interested. All Doug wanted to do was sleep."


TRIM(Text, string to trim- if not included is space)

Removes leading and trailing spaces from a string. Does not affect spaces inside the string.

TRIM(" Hello there everyone. ")="Hello there everyone."
TRIM(" BC3435 ")="BC3435"


UPPER(Text)

Returns the string in all upper case. Punctuation, numbers and white space are not affected.

UPPER("This string HAS case.")="THIS STRING HAS CASE."


Basic Math

ABS (Number)

Returns the absolute value of a number.

ABS(-56)= 56


MAX (Number1,Number2,Number3...)

Returns the maximum value of a set of values. If only one value is provided, that value is returned.

MAX(56,3,-5)=56
MAX(1.78,456,3)=456


MIN (Number1,Number2,Number3...)

Returns the minimum value of a set of values. If only one value is provided, that value is returned.

MIN(56,3,-5)=-5
MAX(1.78,456,3)=1


RAND()


TRUNC (Number)

Returns an integer value by truncating after the decimal for a real number. TRUNC(3.45345)=3


Trigonometry

COS (Angle in radians)

Returns the cosine of a given angle (angle expressed in radians)


PI()

Returns the value PI as 3.14159265358979


SIN (Angle in radians)

Returns the sine of a given angle (angle expressed in radians)


TAN(Angle in radians)

Tangent function.


BooleanLogic

AND([True False Expression 1], [True False Expression 2], …)

Logical AND operation.

AND(TRUE,TRUE)=TRUE
AND(FALSE,TRUE)=FALSE


IF ([Logical test],Value if True, Value if False)

The IF statement evaluates a logical expression, and depending on the result, returns one of two values.

IF(TRUE,"A",56.78)="A"
IF(FALSE,"A",56.78)=56.78
IF([Original.COL1]>5,"Is More than 5","Is Less or equal to 5")


ISERROR(Value to test)

Returns TURE if the value specified is an error. This function can be used with an IF statement to avoid errors, or put in place a default value.

IF(ISERROR([a]/[b]),0,[a]/[b])


ISNULL(Value to check)

Returns TRUE if the value specified is NULL. Note that an empty string does not qualify as NULL.

ISNULL("")=FALSE
ISNULL(NULL)=TRUE

This function is very useful to test for null and provide a default value (for example in cases where a JOIN has returned null values)

IF(ISNULL([COL1]),"Default",[COL1])


NOT([True False Expression])

Returns the opposite logical value to the value specified.

NOT(TRUE)=FALSE
NOT(FALSE)=TRUE


OR([True False Expression 1], [True False Expression 2], …)

Logical OR operation.

OR(TRUE,FALSE)=TRUE
OR(FALSE,TRUE)=TRUE
OR(FALSE,FALSE)=FALSE
OR(TRUE,TRUE)=TRUE


Date Functions

DATE(Year,Month,Day)

Returns a date with the specified year, month,day.

DATE(2010,8,15)=Date time object set to Aug 15 2010 , 0h 0m 0s


DAY(Date value)

Returns the date from a Date Time object

Day(Date(2010,8,15))=15


HOUR(Date or Time value)

Returns the hour portion of a date time value. For dates by default, the hour is zero.


MINUTE(Date or Time value)

Returns the minutes from a date time value. If only a date is used, the minute returned will be zero.


MONTH(Date value)

Returns the month from a date time value


NOW()

Returns the current Date time at the instant the function is evaluated. If you want to have all the records in a given set have the same date time value for a given run, use the RUNTIME() function instead.


SECOND(Date or Time Value)

Returns the seconds from a date time, for a date will return zero.


TODAY()

Returns the current days date.


YEAR (Date value)

Returns the year of a date time value.


YYYY_MM(Date value)

Returns a string of the format YYYY_MM for the specified date time value. Very useful in generating data for graphing and display, as the resulting string sorts dates in order.

YYYY_MM(date(2009,12,25))="2009_12"
YYYY_MM(date(2009,1,28))="2009_01"


Conversion

ISDateTime(value to test)

Returns TRUE if the value specified is a date time object


ISLogical(value to test)

Returns TRUE if the value is a TRUE or FALSE value.

ISLogical(TRUE)=TRUE
ISLogical(FALSE)=TRUE
ISLogical("This is a string")=FALSE


ISNumber(value to test)

Returns TRUE if the value specified is a number. This function will not convert strings to numbers even if they are valid, and if the calculation results in an error (such as divide by zero) this function will return FALSE.

ISNUMBER("123")=FALSE
ISNUMBER(123)=TRUE
ISNUMBER(VALUE("123"))=TRUE
ISNUMBER(VALUE("ABC"))=FALSE
ISNUMBER(5/0)=FALSE


ISText(value to test)

Returns TRUE if the value specified is a text string.

ISText("Hello there!")=TRUE
ISText(45*9)=FALSE


StringToDate(Text string,Mask String)

Converts a string to a date using a mask. Examples of valid masks are:

STRINGTODATE("3/25/2010","MM/dd/yyyy")
STRINGTODATE("30/3/2010","dd/MM/yyyy")
STRINGTODATE("2/2/2010 10:20:23 PM","MM/dd/yyyy HH:mm:ss tt")


TEXT(value to convert to text)

Converts a value to a text string.

TEXT(123)="123"
TEXT(Date(2010,1,1,))="1/1/2010 12:00:00 AM"
TEXT(45>5)="True"

It should be noted that dates are converted based on the settings for the windows installation, so format will vary.


TOLOGICAL(value to convert to true false)

Converts a value to a logical value if possible:


Value(Text)

Converts a string to a numeric value if possible.

VALUE("4534")=4524
VALUE("$99.99")=99.99
VALUE("56%")=56


DataProfiling

DPFieldFormat(Text to find field format for)

Returns a string that represents the format of the provided string, substituting the character "a" for all letters, and "n" for all numbers. Other characters are unaffected.

DPFieldFormat("(555) 555-123-456Z")="(nnn) nnn-nnn-nnna"


DPFormatMatch(Text, text field format to test)

Returns TRUE if the result of the field format for both strings is identical (following the same rules as DPFieldFormat).

DPFormatMatch("12AB","99th")=TRUE
DPFormat("1A","CAT")=FALSE


System Parameters

ROWID()

Returns an integer that defines the row index in the dataset. This can be used within the canvas, but should not be used outside of the canvas or stored as it will not be consistent between runs, or even when switching between preview and full data modes. It can be used as a unique row key within a canava, however, to join rows back to a source data set after some transformation or filtering is done, and is more efficent than creating a concatenated row key.


RunTime()

Provides the date and time of the current run. This is very useful to have a unique timestamp for all result sets that will be common, regardless of how long a canvas takes to calculate. It should be used rather than NOW() whenever it is useful to have a single value accross all data outputs regardless of order created.