String Functions

<< Click to Display Table of Contents >>

Collect > Collect Enterprise > Form Designer Page > Formula Builder > Functions >

String Functions

The String functions are only able to compute string field data. 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.

 

Note: All string checks are case insensitive, unless noted otherwise.

 

ADDLEADING

The ADDLEADING function adds the character(s) defined in the third parameter to the beginning of the first parameter string section, matching the string length 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.

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

Aggregate = No

 

Example 1

FieldA

arthSoft

 

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

FieldA

Testing

 

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

FieldA

arthSoft, Inc.

 

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(s) defined in the third parameter to the first parameter, matching the string length 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 trailing characters.

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

Aggregate = No

 

Example 1

FieldA

EarthSof

 

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

FieldA

Testing

 

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

FieldA

TestingDemo

 

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.

CONCAT

The CONCAT function concatenates a set of parameters, with or without separator(s).

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

Aggregate = No

 

Example 1

FieldA

FieldB

FieldC

EarthSoft

2019

Demo

 

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

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

 

Example 2

FieldA

FieldB

FieldC

EarthSoft

2019

Demo

 

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.

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

Aggregate = No

 

Example 1

FieldA

FieldB

FieldC

FieldD

soft

EarthSoft

2019

Demo

 

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

FieldA

FieldB

FieldC

FieldD

Softer

EarthSoft

2019

Demo

 

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 (as a single entity) is found within any of the following parameters.

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

Aggregate = No

 

Example 1

FieldA

FieldB

FieldC

FieldD

EARTHSOFT

EarthSoft

2019

Demo

 

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

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

 

Example 2

FieldA

FieldB

FieldC

FieldD

Soft

EarthSoft

2019

Demo

 

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

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

COUNTLIST

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

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

Aggregate = No

 

Example 1

FieldA

FieldB

FieldC

FieldD

EARTHSOFT

EarthSoft

2019

Demo

 

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

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

 

Example 2

FieldA

FieldB

FieldC

FieldD

EARTHSOFT

2019

2019

Demo

 

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

FieldA

FieldB

FieldC

FieldD

 

EarthSoft

2019

Demo

 

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

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

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.

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

Aggregate = Yes

 

Example 1 – Collect Form1

FieldA

EarthSoft

2019

Demo

 

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

This formula is combining the FieldA parameter values from each record and using the default separator (a comma).

 

Example 2 – Collect Form1

FieldA

EarthSoft

2019

Demo

 

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

This formula is combining the FieldA parameter values from each record and using the specified separator (a hyphen).

LEN

The LEN function returns the number of characters in the specified parameter.

LEN(<Parameter_1>)

Aggregate = No

 

Example

FieldA

EarthSoft

 

LEN([FieldA]) = 9

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

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.

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

Aggregate = No

 

Example 1

FieldA

FieldB

FieldC

FieldD

prints

footprints

2018

Example

 

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

FieldA

FieldB

FieldC

FieldD

Prints

footprints

2018

Example

 

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

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

REPLACE

The REPLACE function replaces the instances of the second parameter with the third parameter string in the first parameter string.

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

Aggregate = No

 

Example

FieldA

EarthSoft

 

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.

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.

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

Aggregate = No

 

Example 1

FieldA

EarthSoft

 

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

 

Example 2

FieldA

01/01/2018

 

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

TOLOWER

The TOLOWER function converts a specified string parameter to lower case.

TOLOWER(<StringParameter_1>)

Aggregate = No

 

Example

FieldA

EarthSoft

 

TOLOWER([FieldA]) = earthsoft

TOUPPER

The TOUPPER function converts a specified string parameter to upper case.

TOUPPER(<StringParameter_1>)

Aggregate = No

 

Example

FieldA

EarthSoft

 

TOUPPER([FieldA]) = EARTHSOFT

TRIM

The TRIM function removes any initial and trailing blanks from a string parameter.

TRIM(<StringParameter_1>)

Aggregate = No

 

Example

FieldA

        EarthSoft

 

TRIM([FieldA]) = EarthSoft

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