<< Click to Display Table of Contents >> Navigation:  Collect > Collect Enterprise > Template Designer Page > Formula Builder > Functions > String Functions

The String functions are used to process string values or return string values. Within EQuIS Collect, the functions perform calculations as data are entered in forms on the Mobile app. Within EQuIS Link, the functions provide a means to transform data when loading to a selected format.  

 

Below is a descriptive list of each string function and its operations. The examples are applicable to either fields in a Collect form or an EQuIS Link data source, unless otherwise noted.

 

 

ADDLEADING

The ADDLEADING function adds the character defined in the third parameter to the beginning of the first parameter string, until the string length reaches the size specified by the second parameter. If the first parameter string value has more characters than indexed by the second parameter, the function will return the initial first parameter string value without adding any leading characters but will not truncate the string. If any of the parameters are NULL or if the third parameter cannot be converted to a CHAR, then the function will return NULL.

ADDLEADING(<StringParameter_1>,<NumericParameter_1>,<StringParameter_2>)

Aggregate = No

 

Example 1

 

ADDLEADING([FieldA],9,'E']) = EarthSoft

This formula is adding the character "E", as specified in the third parameter, to the beginning of FieldA until 9 characters, as specified in the second parameter, are attained.

 

Example 2

 

ADDLEADING([FieldA],9,'3') = 33Testing

This formula is adding the character "3", as specified in the third parameter, to the beginning of FieldA until 9 characters, as specified in the second parameter, are attained.

 

Example 3

 

ADDLEADING([FieldA],9,'E']) = arthSoft, Inc.

This formula returns only the value of FieldA (i.e., first parameter) as it exceeds 9 characters, as specified in the second parameter.

ADDTRAILING

The ADDTRAILING function adds the character defined in the third parameter to the end of the first parameter, until the string's length is the value specified by the second parameter. If the first parameter string value has more characters than defined by the second parameter, the function will return the initial first parameter string value without adding any trailing characters nor truncating the string. If any of the parameters are null, or if the third parameter cannot be converted to a CHAR, then the function returns NULL.

ADDTRAILING(<StringParameter_1>,<NumericParameter_1>,<StringParameter_2>)

Aggregate = No

 

Example 1

 

ADDTRAILING([FieldA],9,'t']) = EarthSoft

This formula is adding the character "t", as specified in the third parameter, to the end of FieldA until 9 characters, as specified in the second parameter, are attained.

 

Example 2

 

ADDTRAILING([FieldA],9,3) = Testing33

This formula is adding the character "3", as specified in the third parameter, to the end of FieldA until 9 characters, as specified in the second parameter, are attained.

 

Example 3

 

ADDTRAILING([FieldA],9,0) = TestingDemo

This formula returns only the value of FieldA (i.e., first parameter) as it exceeds 9 characters, as specified in the second parameter.

BASE64

The BASE64 function converts (encodes) the given parameter to a BASE64 string equivalent. If the parameter cannot be converted to a string or is NULL, then the function returns NULL.

BASE64(<Parameter_1>)

Aggregate = No

 

Example 1

 

BASE64([FieldA]) = RQBhAHIAdABoAFMAbwBmAHQA

This formula is converting (encoding) FieldA and returning the converted (encoded) equivalent BASE64 string.

CLEAN

The CLEAN function removes all control characters (less than number 32) from the string parameter. Optional second parameter defaults to TRUE and removes spaces. Optional third parameter defaults to TRUE and replaces accents with unaccented characters. Optional fourth parameter defaults to TRUE and forces uppercase. If the first parameter is NULL, then the function returns NULL. If other parameters are present but cannot be converted to Boolean, then they will be FALSE.

CLEAN(<StringParameter_1>,{<OptionalParameter_1>,<OptionalParameter_2>,<OptionalParameter_3>})

Aggregate = No

 

Example 1

 

CLEAN([FieldA]) = EARTHMOON

 

Example 2

 

CLEAN([FieldA],FALSE()) = EARTH MOON

 

Example 3

 

CLEAN([FieldA],FALSE(),TRUE(),FALSE()) = Earth Moon

CONCAT

The CONCAT function concatenates a set of parameters, with or without separator(s). Ignores any NULL parameters and if no parameters contain valid strings, returns an empty string.

CONCAT(<StringParameter_1>,<StringParameter_2>…<StringParameter_N>)

Aggregate = No

 

Example 1

 

CONCAT([FieldA],[FieldB],[FieldC]) = EarthSoft2019Demo

This formula is joining together FieldA, FieldB, and FieldC.

 

Example 2

 

CONCAT([FieldA],'-',[FieldB],'-',[FieldC]) = EarthSoft-2019-Demo

This formula is joining together FieldA, FieldB, and FieldC along with the specified separators.

CONTAINS

The CONTAINS function returns TRUE if the first parameter is found, partially or fully, within any of the following parameters. This is case insensitive. Returns NULL if the first parameter is NULL or if a NULL value is encountered before a match is found. If no match is found and all parameters are not null, the function returns FALSE.

CONTAINS(<Parameter_1>,<Parameter_2>…<Parameter_N>)

Aggregate = No

 

Example 1

 

CONTAINS([FieldA],[FieldB],[FieldC],[FieldD]) = TRUE

This formula is searching FieldB, FieldC, and FieldD to determine if they contain the parameter value "soft" (FieldA).

 

Example 2

 

CONTAINS([FieldA],[FieldB],[FieldC],[FieldD]) = FALSE

This formula is searching FieldB, FieldC, and FieldD to determine if they contain the parameter value "Softer" (FieldA).

CONTAINSWORD

The CONTAINSWORD function returns TRUE if the first parameter string is found within any of the following parameters as a whole word. The function returns NULL if the first parameter is NULL or if a NULL value is encountered before a match is found.

CONTAINSWORD(<StringParameter_1>,<Parameter_2>…<Parameter_N>)

Aggregate = No

 

Example 1

 

CONTAINSWORD([FieldA],[FieldB],[FieldC],[FieldD]) = TRUE

This formula is searching FieldB, FieldC, and FieldD to determine if they contain the word "EARTHSOFT" (FieldA).

 

Example 2

 

CONTAINSWORD([FieldA],[FieldB],[FieldC],[FieldD]) = FALSE

This formula is searching FieldB, FieldC, and FieldD to determine if they contain the word "Soft" (FieldA). Although "EarthSoft" contains "Soft", since it is not separated from the "Earth" with a space, "Soft" is not considered to be word within EarthSoft.

 

Example 3

 

CONTAINSWORD([FieldA],[FieldB],[FieldC],[FieldD]) = TRUE

This formula is searching FieldB, FieldC, and FieldD to determine if they contain the word "Soft" (FieldA). In this case, "Earth Soft" contains two words, "Earth" and "Soft". So the result is TRUE.

COUNTCHAR

The COUNTCHAR function counts the number of times the first chararacter parameter is found within the following parameters. Returns NULL if the first parameter is null and ignores any other null parameters. Otherwise, the function returns the number of times the first parameter was found in the subsequent parameters.

COUNTCHAR(<CharParameter>,<StringParameter_1>,{<OptionalStringParameter_2>}...{<OptionalStringParameter_N>})

 

Aggregate = No

 

Example 1

 

COUNTCHAR([FieldA],[FieldB]) = 3

This formula is searching FieldB to determine if it contains the parameter value "t" (FieldA).

 

Example 2

 

COUNTCHAR([FieldA],[FieldB],[FieldC],[FieldD],[FieldE]) = 3

This formula is searching FieldB, FieldC, FieldD, and FieldE to determine if they contain the parameter value "t" (FieldA).

 

Example 3

 

COUNTCHAR([FieldA],[FieldB],[FieldC],[FieldD],[FieldE]) = NULL

This formula returns NULL since FieldA is empty.

COUNTLIST

The COUNTLIST function counts all the comma-delimited codes in the specified list of parameters, ignores null parameters and duplicated parameters, and always returns a number.

COUNTLIST(<Parameter_1>,<Parameter_2>…<Parameter_N>)

Aggregate = No

 

Example 1

 

COUNTLIST([FieldA],[FieldB],[FieldC],[FieldD]) = 4

This formula is counting the number of parameters passed, FieldA, FieldB, FieldC and FieldD.

 

Example 2

 

COUNTLIST([FieldA],[FieldB],[FieldC],[FieldD]) = 3

This formula is counting the number of parameters passed (FieldA, FieldB, and FieldD), but ignores FieldC, which is a duplicate of FieldB.

 

Example 3

 

COUNTLIST([FieldA],[FieldB],[FieldC],[FieldD]) = 5

This formula is counting the number of codes passed in (FieldB, FieldC, and FieldD), but ignores FieldA, which is a null. Note that FieldB and FieldD contain two codes each.

GUID

The GUID function generate a global unique identifier string.

GUID()

Aggregate = No

 

Example

GUID() = e9c5badc-bede-4bb7-8e59-d4663e2a28af

This formula is returning a global unique identifier string.

JOIN

The JOIN function combines the value of the first parameter evaluated on each record and uses an optional delimiter specified in the second parameter. If no delimiter is specified, then a comma is used. The function also uses an optional third parameter to sort the list in ascending or descending alphanumeric order. If the third parameter is set to TRUE(), the sort order is ascending, and if the parameter is set to FALSE(), the sort order is descending. If the third parameter is not set, the default value is TRUE(). This function does not remove duplicate values or codes. Note that to be able to set the third parameter, the second parameter is no longer optional and must be set. The function ignores null or empty strings and always returns NULL if no records were found.

JOIN(<Parameter_1>,{<OptionalParameter_1>},{<OptionalBooleanParameter_2>})

Aggregate = Yes

 

Example 1 – Collect Form1

 

JOIN([form1.FieldA],' | ',FALSE()) = W-93 | M-19 | E-24 | B-32

This formula is combining the FieldA values from each record using the bar separator and sorting the values in descending order.

 

Example 2 – Collect Form1

 

JOIN([form1.FieldA]) = 2019,Demo,Demo,EarthSoft

This formula is combining the FieldA values from each record using the default separator (a comma) and default sorting order (ascending). Note that duplicate values are retained.

LEN

The LEN function returns the number of characters in the specified parameter converted to a string. If the parameter is NULL, then the function returns NULL.

LEN(<Parameter_1>)

Aggregate = No

 

Example

 

LEN([FieldA]) = 9

This formula is calculating the number of characters in the FieldA parameter value.

NUMVALUE

The NUMVALUE function examines the specified string parameter and returns the first section if numeric. Otherwise, the function returns null. The optional second parameter can be used to define the decimal separator. If the second parameter is NULL, then the decimal point is used. If the first parameter does not start with a numeric part, then the function returns NULL.

NUMVALUE(<StringParameter_1>,{<OptionalParameter_1>})

Aggregate = No

 

Example 1

 

NUMVALUE([FieldA]) = 2019

 

Example 2

 

NUMVALUE([FieldA],',') = 2019.04

POS

The POS function returns the position of the first string parameter within the second string parameter. The first character position is considered zero (0). Therefore, a string parameter with 4 characters would contain positions 0, 1, 2, and 3. The function searches for the first instance of first string parameter passed (see Example 3). If either of the parameters are NULL, then the function returns NULL.

POS(<StringParameter_1>,<StringParameter_2>)

Aggregate = No

 

Example 1

 

POS([FieldA],[FieldB]) = 2

 

Example 2

 

POS([FieldA],[FieldB]) = 1

 

Example 3

 

POS([FieldA],[FieldB]) = 3

 

Example 4

 

POS([FieldA],[FieldB]) = 5

RANDSTR

The RANDSTR function generates a UTF8 string of random characters. The first parameter defines the length of the string to be generated. The optional second and third parameters define the minimum and maximum ASCII character codes to be used. By default or if these parameters are NULL or cannot be converted to integers, minimum and maximum character codes correspond to the range of ASCII characters from 60 to 90, which includes all uppercase letters and the symbols (<=>?). If the first parameter is NULL or cannot be converted to an integer value, then the function returns NULL.

RANDSTR(<NumericParameter_1>,{<OptionalNumericParameter_1>},{<OptionalNumericParameter_2>})

Aggregate = No

 

Example 1

 

RANDSTR([FieldA]) = KAYMQV

This formula is returning is a random string of six ASCII characters.

 

Example 2

 

RANDSTR([FieldA]) = E>OTIWW=EKO?a

This formula is returning is a random string of 13 ASCII characters.

 

Example 3

 

RAND([FieldA],[FieldB]) = YHQLLJOS

This formula is returning is a random string of eight ASCII characters starting at character code 72.

 

Example 4

 

RAND([FieldA],[FieldB],[FieldC]) = WWSRTQVVVPQQWO

This formula is returning is a random string of 14 ASCII characters between character codes 78 to 88.

REGEX

The REGEX function uses the first parameter as a Regular Expression (REGEX) and searches for a match in the subsequent parameter values. The function returns TRUE if a match is found. Otherwise, the function returns FALSE. The REGEX function is case sensitive. If the first parameter is NULL or the regular expression is invalid, the function returns NULL. If a NULL value is encountered before a match is found, the function returns FALSE.

REGEX(<Parameter_1>,<Parameter_2>…<Parameter_N>)

Aggregate = No

 

Example 1

 

REGEX([FieldA],[FieldB],[FieldC],[FieldD]) = TRUE

This formula is searching FieldB, FieldC, and FieldD to determine if they contain the parameter value "prints" (FieldA).

 

Example 2

 

REGEX([FieldA],[FieldB],[FieldC],[FieldD]) = FALSE

This formula is searching FieldB, FieldC, and FieldD to determine if they contain the parameter value "Prints" (FieldA).

 

Example 3

 

REGEX([FieldA],[FieldB],[FieldC]) = FALSE

REGEX([FieldA],[FieldD]) = TRUE

The first formula is checking if FieldB and FieldC do not use the “word”. Since they both do, no matches are found.

The second formula checks the same condition on FieldD, which does not use “word”, and therefore the match is found and the function returns TRUE.

Regular expressions are well documented on internet. For example, the regextester.com site can be used to test the conditions under which they will return a match.

REPLACE

The REPLACE function replaces the instances of the second parameter with the third parameter string in the first parameter string. If the first or second parameters are NULL, then the function returns NULL. If the third parameter is NULL, the function removes instances of the second parameter string in the first one.

REPLACE(<StringParameter_1>,<StringParameter_2>,<StringParameter_3>)

Aggregate = No

 

Example

 

REPLACE([FieldA],'E',3) = 3arthSoft

This formula is acquiring the value of the first parameter and replacing the character "E", as specified in the second parameter, with the character "3", as specified in the third parameter.

SORT

The SORT function controls the order in which a list of comma-separated strings are displayed. The first parameter is a comma-separated list of strings, the optional second parameter is the sort type containing two options, "NATURAL" or "ALPHABETICAL", with "NATURAL" set as the default setting. The optional third parameter is the sort order, containing two options, "ASC" (ascending) or "DESC" (descending), with "ASC" set as the default setting.

SORT(<StringParameter_1>,{<OptionalStringParameter_1>},{<OptionalStringParameter_2>})

Aggregate = No

 

Example 1

 

SORT([FieldA]) = air,EarthSoft,environment,geography,water

This formula is sorting the values using the default settings of "Natural" and "ASC" sort order.

 

Example 2

 

SORT([FieldA],'ALPHABETICAL') = air1,air12,air2,EarthSoft,environment,geography,water

This formula is sorting the values using the "Alphabetical" and "ASC" sort order.

 

Example 3

 

SORT([FieldA],'NATURAL','DESC') = geography,EarthSoft,air2 ,air1, water, environment, air12

This formula is sorting the values, including spaces, using the "Natural" and "DESC" sort order.

 

 

Example 4

 

SORT(DISTINCT([FieldA],[FieldB],[FieldC],[FieldD]),'ALPHABETICAL','DESC') = geography,EarthSoft,air

This formula is sorting the distinct values for multiple fields using the "Alphabetical" and "DESC" sort order. DISTINCT will ignore duplicate values, "geography" in this example.

SPLIT

The SPLIT function separates the first parameter string using the second parameter character and returns the string corresponding to the index defined by the third integer parameter. If any of the parameters are NULL, then the function returns NULL. If the second parameter cannot be converted to a CHAR or the third parameter cannot be converted to a valid index, the function also returns NULL.

SPLIT(<StringParameter_1>,<Parameter_1>,<NumericParameter_1>)

Aggregate = No

 

Example 1

 

SPLIT([FieldA],'h',1) = Soft

 

Example 2

 

SPLIT([FieldA],'/',2) = 2018

SUBSTR

The SUBSTR function returns a substring from a source string given in the first parameter. The substring starting position is defined in the second parameter. An optional substring length is defined in the third parameter. The first string parameter position is considered zero (0). If any of the parameters are NULL or if the start position is outside the string, then the function returns NULL. If the length is not specified, then the function returns the string to the right of the start position including the start position.

SUBSTR(<StringParameter_1>,<NumericParameter_1>,{<NumericParameter_2>})

Aggregate = No

 

Example 1

 

SUBSTR([FieldA],1,2) = es

 

Example 2

 

SUBSTR([FieldA],5,4) = Soft

TOLOWER

The TOLOWER function converts a specified string parameter to lower case. If the parameter is NULL, then the function returns NULL.

TOLOWER(<StringParameter_1>)

Aggregate = No

 

Example

 

TOLOWER([FieldA]) = earthsoft

TOUPPER

The TOUPPER function converts a specified string parameter to upper case. If the parameter is NULL, then the function returns NULL.

TOUPPER(<StringParameter_1>)

Aggregate = No

 

Example

 

TOUPPER([FieldA]) = EARTHSOFT

TRIM

The TRIM function removes any initial and trailing blanks from a string parameter. If the parameter is NULL, then the function returns NULL.

TRIM(<StringParameter_1>)

Aggregate = No

 

Example

 

TRIM([FieldA]) = EarthSoft

This formula is searching FieldA and removes the initial empty spaces before returning the string value 'EarthSoft'.

UNITVALUE

The UNITVALUE function examines the string in the first parameter and returns the characters after the first numeric portion of the string. The optional second parameter can be used to define the characters to be filtered out and the third parameter can be used to define the decimal separator. If the first parameter is NULL or if no unit is found (only have a number), then the function returns NULL. Note that the final result is always trimmed to remove whitespace.

UNITVALUE(<StringParameter_1>,{<OptionalParameter_1>},{<OptionalParameter_2>})

Aggregate = No

 

Example 1

 

UNITVALUE([FieldA]) = m

 

Example 2

 

UNITVALUE([FieldA],'[]') = ft

 

Example 3

 

UNITVALUE([FieldA],'[]',',']) = [ft]