Expressions & Functions

Expressions are combinations of functions that can be evaluated anywhere token replacement is available. The available expression types are String, Integer, Float, and Time. The function arguments for each type of expression will be coerced to that type when evaluated. EG: Integer type would coerce "15.24" to 15

[!String!  SOME_FUNCTION([first_name])  !!]
[!Integer!  SOME_FUNCTION([amount], [other_amount])  !!]
[!Float!  SOME_FUNCTION([currency_token])  !!]
[!Time!  SOME_FUNCTION([date_token], [another_date_token])  !!]

Example Data

Given the following example data, the expressions below would output the values as indicated by =>

{
  "first_name": "John",
  "last_name": "Smith",
  "birth_year": "1977",
  "yob": 2012,
  "mortgage_amount": 65000,
  "debt_amount": 8283.25,
  "started_at_utc": "2020-06-21 22:35:12 UTC"
}

String Example

Convert function arguments into strings and evaluate the expression.

[!String! ALPHANUMERIC_DASH(CONCAT([first_name], " ", [last_name])) !!]
=> john-smith

Integer Example

Convert function arguments into integers and evaluate the expression.

[!Integer! MIN([birth_year], [yob]) !!]
=> 1977

Decimal Example

Convert function arguments into decimals and evaluate the expression.

[!Float! SUM([debt_amount], [mortgage_amount]) !!]
=> 73283.25

Time Example

Convert function arguments into Time and evaluate the expression.

[!Time! DATE_FORMAT(DATE_ADD([started_at_utc], 86400), "%Y-%m-%d %M-%S") !!]
=> "2020-06-22T18:35:12"

case(statement)

A CASE statement that allows you to evaluate complex conditional logic.

CASE('apple' WHEN 'apple' THEN 1 WHEN 'banana' THEN 2 ELSE 3 END)
=> 1

if(statement, output_when_true, output_when_false)

Evaluate a statement and output a value when true or false.

IF(15 < 10, 10, 20)
=> 20

avg(*values)

Get the average of the passed numeric values.

AVG(1,2,3,4)
=> 2.5

count(*values)

Count the passed values.

COUNT(1,2,3,4)
=> 4

max(*values)

Get the largest numeric value from the set of passed arguments.

MAX(1,2,3,4)
=> 4

min(*values)

Get the smallest numeric value from the set of passed arguments.

MIN(1,2,3,4)
=> 1

round(value, precision)

Returns float rounded to the nearest value.

ROUND(8.2)
=> 8

rounddown(value, precision)

Returns float rounded down to the nearest value.

ROUNDDOWN(1.234)
=> 1

roundup(value, precision)

Returns float rounded up to the nearest value.

ROUNDUP(1.234)
=> 2

sum(*values)

Get the sum of the numeric values.

SUM(1,2,3,4)
=> 10

alphanumeric_dash(value)

Returns a copy of the receiver with only alphanumeric characters (0-9 and a-z A-Z) and spaces converted to dashes. Leading and trailing spaces are removed.

ALPHANUMERIC_DASH(" ./;!!!]  hello waffle world!@#$%^&*($)  ")
=> "hello-waffle-world"

alphanumeric_underscore(value)

Returns a copy of the receiver with only alphanumeric characters (0-9 and a-z A-Z) and spaces converted to underscores. Leading and trailing spaces are removed.

ALPHANUMERIC_UNDERSCORE(" ./;!!!]  hello waffle world!@#$%^&*($)  ")
=> "hello_waffle_world"

base64_decode(value)

Returns the Base64-decoded version of str. This method complies with RFC 2045. Characters outside the base alphabet are ignored.

BASE64_DECODE('dGVzdA==')
=> 'text'

base64_encode(value)

Returns the Base64-encoded version of bin. This method complies with RFC 2045. Line feeds are added to every 60 encoded characters.

BASE64_ENCODE('text')
=> 'dGVzdA=='

concat(*values)

CONCAT('AB', 'CD', 'EF')
=> "ABCDEF"

contains(search, value)

CONTAINS('ABCD', 'A')
=> true

date_add(value, seconds)

The date_add function adds seconds to a date.

DATE_ADD([started_at_utc], 86400)
=> "2020-06-29 11:34:25 -0400"

date_format(value, format)

Format a date

DATE_FORMAT([started_at_utc], "%Y-%m-%d %M-%S")
=> "2020-06-22T18:35:12"

date_parse(value, format)

The date_parse function is a natural language date/time parser.

DATE_PARSE("30 days from now")
=> "2020-07-29 15:42:57 UTC"

date_subtract(time, time_or_decimal)

The date_subtract function subtracts another timestamp or decimal from the first argument.

DATE_SUBTRACT([current_time_utc], [started_at_utc])
=> "2020-06-29 11:34:25 -0400"

date_to_time_zone(value, time_zone)

The date_to_time_zone returns a copy of the receiver in the given time zone.

DATE_TO_TIME_ZONE([lead_created_at], "Eastern Time (US & Canada)")
=> "2020-07-29 15:42:57"

digest_md5(value)

A method for calculating message digests using the MD5 Message-Digest Algorithm by RSA Data Security, Inc., described in RFC1321. MD5 calculates a digest of 128 bits (16 bytes).

DIGEST_MD5('text')
=> 90015098...

digest_sha1(value)

A method for calculating message digests using the SHA-1 Secure Hash Algorithm by NIST (the US' National Institute of Standards and Technology), described in FIPS PUB 180-1.

DIGEST_SHA1('text')
=> a9993e36...

digest_sha2(value)

A method for calculating SHA256 which works on chunks of 512 bits and returns a 256-bit digest (SHA256)

DIGEST_SHA2('text')
=> ba7816bf8...

downcase(value)

Returns a copy of the receiver with all letters converted to lowercase.

DOWNCASE("HELLO World")
=> "hello world"

find(search, value)

FIND('BC', 'ABCD')
=> 2

generate_uuid()

Returns a random UUID (Universally Unique Identifier).

GENERATE_UUID()
=> "518e8221-a29e-72c1-a716-486156481234"

left(value, length)

LEFT('ABCD', 2)
=> "AB"

len(value, length)

LEN('ABCD')
=> 4

oauth_access_token(oauth_connection_key)

Returns an Access Token for an OAuth Connection.

OAUTH_ACCESS_TOKEN('marchex_v2')
=> "sdjf9032fj239fj90sjf90wjf390"

random(min, max)

Returns a random number between min and max

RANDOM(0, 1000)
=> 50

right(value, length)

RIGHT('ABCD', 2)
=> "CD"

seconds_to_hms(seconds)

Returns a copy of the receiver formatted as HH:MM:SS

SECONDS_TO_HMS(3672)
=> "01:01:12"

split(value, pattern, index)

Divides value into substrings based on a delimiter, returning the substring at index.

SPLIT("John Smith", " ", 1)
=> "Smith"

strip(value)

Returns a copy of the receiver with leading and trailing whitespace removed.

Whitespace is defined as any of the following characters: null, horizontal tab, line feed, vertical tab, form feed, carriage return, space.

STRIP("    hello world   ")
=> "hello world"

substitute(value, search, replacement)

SUBSTITUTE('green cat, blue cat, yellow cat', 'cat', 'dog')
=> "green dog, blue cat, yellow cat"

substitute_all(value, search, replacement)

Replace all occurrences of search with replacement in value.

SUBSTITUTE_ALL("green cat, blue cat, yellow cat", "cat", "dog")
=> "green dog, blue dog, yellow dog"

substring(value, start, length)

A substring is a range of characters within an existing string.

SUBSTRING([started_at_offer_time_zone], 0, 9)
=> "2020-09-20"

titleize(value)

Returns a copy of the receiver with the first letter of each word capitalized.

TITLEIZE("  hello world  ")
=> "  Hello World  "

to_currency(value, symbol)

Converts a numeric value into a properly formatted currency string, including thousands separators and a currency symbol.

TO_CURRENCY("1234567.89")
=> "$1,234,567.89"

TO_CURRENCY("1234567.89", "€")
=> "€1,234,567.89"

to_formatted_number(args, **, block)

Returns a copy of the receiver as a phone number in the local format.

TO_FORMATTED_NUMBER(" + 1 719-852-2985 ")
=> "(719) 522-0377"

to_integer(value)

Returns a copy of the receiver as an integer.

TO_INTEGER(' 15.15 ')
=> 15

to_json(value)

Returns a copy of the receiver as a JSON string.

TO_JSON({example: "value"})
=> {"example": "value"}

to_phone_number(value)

Returns a copy of the receiver as a normalized international phone number.

TO_PHONE_NUMBER(" 1 (719) 852 2985 ")
=> "+17198522985"

upcase(value)

Returns a copy of the receiver with all letters converted to uppercase.

UPCASE("Hello World")
=> "HELLO WORLD"

url_decode(value)

Returns a copy of the receiver with all percent (%) signs followed by two hex digits replaced with the corresponding character.

URL_DECODE("hello%20world%2C%20how%20are%20you%3F")
=> "hello world, how are you?"

url_encode(value)

Returns a copy of the receiver with all non-alphanumeric characters replaced with a percent (%) sign followed by two hex digits.

URL_ENCODE("hello world, how are you?")
=> "hello%20world%2C%20how%20are%20you%3F"

usa_zip_code(value)

Returns a copy of the receiver with only the first 5 digits preserved.

USA_ZIP_CODE("USA 90210 OR")
=> "90210"