Text Functions

Concatenate

Description

1. Appends two or more strings together to form a single value.

Syntax

Examples

1. CONCATENATE("I","<",3,"Data") returns I<3Data

2. CONCATENATE(source.order_no,"-",source.payment_status) returns the order number and payment status from a source, separated by a hyphen (-)

Ends With

Description

1. Returns whether a string (or list of strings) ends with a given suffix. Returns TRUE or FALSE (or a list).

Syntax

Examples

1. ENDS_WITH("foo","o") returns TRUE

2. ENDS_WITH(["foo","bar","baz"],"o") returns [TRUE, FALSE, FALSE]

Find and Search

Description

1. Returns the first location of the search string within the value using a case-sensitive search.

Syntax

1. This is a 1-based function, meaning the first position in a string is 1

2. starting_at is optional and defaults to position 1

3. This expression returns the position irrespective of the starting_at position (see examples below)

4. This expression returns 0 if the text_to_search is not found in the search_for string

Examples

1. FIND("f","foo") and SEARCH("f","foo") each return 1

2. FIND("f","foo",2) and SEARCH("f","foo",2) each return an error message ( To ensure that an error message does not break a mapping expression, try wrapping the statement in an If Error function for error handling)

3. FIND("o","foo") and SEARCH("f","foo") each return 2

4. FIND("o","foo",2) and SEARCH("o","foo",2) each return 2

5. FIND("o","foo",3) and SEARCH("o","foo",3) each return 3

6. FIND("f","Foo") returns 0, while Search("f","Foo") returns 1 ( Find is case-sensitive, while Search is case-insensitive)

Hash (SHA256)

Description

1. Returns the hash of a string using the SHA-256 algorithm. This function returns 32 bytes.

Syntax

Examples

1. SHA256("Hello world")returns 64ec88ca00b268e5ba1a35678a1b5316d212f4f366b2477232534a8aeca37f3c

2. SHA256("splett2@splett.net")returns a5ec0df3d9a3b7b98eef1f7766d92b3cda046cdc458dfb8903e856f8c02ddc7d

3. ⚠️SHA256(null)(or any null input) returns an error: Type not supported for SHA256

Join

Description

1. Concatenates the elements of one or more one-dimensional arrays using a specified delimiter.

Syntax

Examples

1. JOIN("-",305,555,2218) returns 305-555-2218

2. JOIN("_",source.first_name,source.last_name) concatenates the first_name and last_name attributes in source data, separated by an underscore (_)

3. JOIN(" > ",source.department,source.category,source.subcategory,source.style) returns a string consisting of [department] > [category] > [subcategory] > [style]

Left

Description

1. Returns a substring from the beginning of a specified string or list of strings.

Syntax

1. number_of_characters is optional and defaults to 1 if not specified

Examples

1. LEFT("David",2) returns Da

2. LEFT("David") returns D

3. LEFT(["David","Copperfield"],2) returns [Da, Co]

Length

Description

1. Returns the length of a string or list.

Syntax

Examples

1. LEN("foo") returns 3

2. LEN("David Copperfield") returns 17

3. LEN(["foofaraw","barbell","bazel"]) returns 3

4. LEN([235,32,24462] returns 3

Lower

Description

1. Converts a specified string to lowercase.

Syntax

Examples

1. LOWER("DAVID COPPERFIELD") returns david copperfield

2. LOWER("David Copperfield") returns david copperfield

Proper

Description

1. Capitalizes each word in a specified string.

Syntax

Examples

1. PROPER("DAVID COPPERFIELD") returns David Copperfield

2. PROPER("david copperfield") returns David Copperfield

Regular Expression Extract

Description

1. Extracts matching substrings according to a regular expression.

Syntax

Examples

1. REGEXEXTRACT("foo","[f]") returns f

2. REGEXEXTRACT(source.id,"[0-9]+") returns all numeric characters from the id attribute in source data

Regular Expression Match

Description

1. Returns whether a piece of text matches a regular expression. Returns TRUE or FALSE.

Syntax

Examples

1. REGEXMATCH("foo","[f]") returns TRUE

2. REGEXMATCH("foo","[0-9]") returns FALSE

3. REGEXMATCH("foo","bar") returns FALSE

Regular Expression Replace

Description

1. Replaces part of a text string with a different text string using regular expressions.

Syntax

Examples

1. REGEXREPLACE("foo123","[0-9]","") returns foo

2. REGEXREPLACE(source.phone_number,"[^0-9]", "") returns only the numeric characters in the phone_number field in the source data

Replace

Description

1. Replaces part of a text string with a different text string, with an optional position parameter to replace text starting at a specific position within a string.

Syntax

1. text is the string in which something will be replaced

2. search is the string within the text to replace

3. replace is the string replacing the the search string

4. position is optional and refers to the position at which the replacement will begin (starting at 1); omit the position parameter to replace a string at any position of the text or an entire string.

Examples

1. REPLACE("foo","o","") returns f

2. REPLACE("SoundCommerce","Commerce","ies",6) returns Soundies

3. REPLACE("SoundCommerce","Commerce","ies",7) returns SoundCommerce

Right

Description

1. Returns a substring from the end of a specified string or list of strings.

Syntax

1. number_of_characters is optional and defaults to 1 if not specified

Examples

1. RIGHT("David",2) returns id

2. RIGHT("David") returns d

3. RIGHT(["David","Copperfield"],2) returns [id, ld]

Split

Description

1. Splits the value by the delimiter, returning a list of all elements that were split. It can optionally only split the first instance of the delimiter. It can also optionally remove any empty results.
2. To select a specific string within the list, add the offset (starting from 1) within square brackets after the function (e.g. to select the first string in the split list, enter SPLIT(value,delimiter)[1]).

Syntax

1. split_by_each is true by default, and determines whether or not to divide value by every instance of the delimiter

2. remove_empty_text is true by default, and determines whether or not to remove empty text messages from the split results

Examples

1. SPLIT("305-555-0882","-") returns [305,555,0882]

2. SPLIT("305-555-0882","-",false) returns [305,555-0882]

3. SPLIT("305-555- 0882","-",true,false) returns [305,555, 0882]

4. SPLIT("305-555-0882","-")[0] returns 305

5. SPLIT(source.input_event_scid,":")[2] returns the third string of the input_event_scid source metafield when separated by a colon (:)

Starts With

Description

1. Returns whether a string (or list of strings) begins with a given prefix. Returns TRUE or FALSE (or a list).

Syntax

Examples

1. STARTS_WITH("foo","f") returns TRUE

2. STARTS_WITH(["foo","bar","baz"],"f") returns [TRUE, FALSE, FALSE]

String

Description

1. Converts any value to a string.

Syntax

Examples

1. STRING(1) returns 1

Switch

Description

1. Tests an expression against a list of cases and returns the corresponding value of the first matching case, with an optional default value if nothing else is met. If no default value is provided, input values that do not match any cases will error

Syntax

Examples

1. SWITCH(1, 1, true, null) returns true

2. SWITCH("remove space","remove-hyphen","removehyphen","remove space","removespace",null) returns removespace

3. SWITCH("remove-hyphen","remove-hyphen","removehyphen",null) returns removehyphen

4. Importance of adding a default:

   1. SWITCH("removespace","remove-hyphen","removehyphen") returns an error

   2. SWITCH("removespace","remove-hyphen","removehyphen",null) returns null

Text Join

Description

1. Concatenates the elements of one or more one-dimensional arrays using a specified delimiter, optionally ignoring nulls.

Syntax

Examples

1. TEXTJOIN("-",true,123,456,"",789) returns 123-456-789

2. TEXTJOIN("-",false,123,456,"",789) returns 123-456--789

To JSON

Description

1. Converts a specified object or JSON blob to a JSON string.

Syntax

Examples

1. TO_JSON({"hello":"goodbye"}) returns "{\"hello\": \"goodbye\"}"
2. TO_JSON("hello") returns an error, because the input is a string and not an object or JSON blob

Trim

Description

1. Removes leading and trailing spaces in a specified string.

Syntax

Examples

1. TRIM("       hello     ") returns hello

Upper

Description

1. Converts a specified string to uppercase.

Syntax

Examples

1. UPPER("david copperfield") returns DAVID COPPERFIELD