Expressions & Functions

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

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 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"

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"