Expressions & Functions

Sample data

The examples on this page use the data below; the result of each expression follows its =>

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

A String expression

Arguments are treated as text before the expression runs.

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

A Time expression

Arguments are treated as times before the expression runs.

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

case(statement)

Tests several conditions in order and returns the output of the first one that is true.

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

if(statement, output_when_true, output_when_false)

Checks a condition and returns one value when it holds and another when it does not.

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

avg(*values)

The mean of the numbers you pass in.

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

count(*values)

How many values were passed in.

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

max(*values)

The largest of the numbers you pass in.

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

min(*values)

The smallest of the numbers you pass in.

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

round(value, precision)

Rounds a number to the closest value at the precision you give.

ROUND(8.2)
=> 8

rounddown(value, precision)

Rounds a number down, toward the lower value, at the precision you give.

ROUNDDOWN(1.234)
=> 1

roundup(value, precision)

Rounds a number up, toward the higher value, at the precision you give.

ROUNDUP(1.234)
=> 2

sum(*values)

Adds the numbers you pass in.

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

alphanumeric_dash(value)

Keeps only letters (a-z, A-Z) and digits (0-9), turns spaces into dashes and trims spaces from both ends. Handy for slugs and IDs.

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

alphanumeric_underscore(value)

Keeps only letters (a-z, A-Z) and digits (0-9), turns spaces into underscores and trims spaces from both ends.

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

base64_decode(value)

Decodes a Base64 string (RFC 2045), skipping any characters outside the Base64 alphabet.

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

base64_encode(value)

Encodes a value as Base64 (RFC 2045), with a line break after 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)

Moves a date forward by a number of seconds.

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

date_format(value, format)

Writes a date in the format you choose.

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

date_parse(value, format)

Reads a date or time written in plain language and turns it into a date.

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

date_subtract(time, time_or_decimal)

Takes a timestamp or a number of seconds away from a date.

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

date_to_time_zone(value, time_zone)

Converts a date into another time zone.

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

digest_md5(value)

Returns the MD5 hash of a value (RFC 1321): a 128-bit (16-byte) digest.

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

digest_sha1(value)

Returns the SHA-1 hash of a value, as defined by NIST in FIPS PUB 180-1.

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

digest_sha2(value)

Returns the SHA-256 hash of a value: a 256-bit digest computed over 512-bit blocks.

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

downcase(value)

Makes every letter lowercase.

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

find(search, value)

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

generate_uuid()

Creates a new universally unique identifier (UUID).

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)

Fetches an access token from one of your OAuth connections, for authenticated webhooks.

OAUTH_ACCESS_TOKEN('marchex_v2')
=> "sdjf9032fj239fj90sjf90wjf390"

right(value, length)

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

seconds_to_hms(seconds)

Shows a number of seconds as HH:MM:SS, for example a call duration.

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

split(value, pattern, index)

Breaks a value apart at a delimiter and returns the piece at the index you ask for.

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

strip(value)

Trims whitespace from the start and end of a value.

Whitespace here means null, horizontal tab, line feed, vertical tab, form feed, carriage return and 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)

Replaces every occurrence of one piece of text with another.

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

substring(value, start, length)

Returns a slice of a string: the characters in the range you specify.

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

titleize(value)

Capitalizes the first letter of every word.

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

to_currency(value, symbol)

Formats a number as money, with a currency symbol and thousands separators.

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

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

to_formatted_number(args, **, block)

Formats a phone number the way it is written locally.

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

to_integer(value)

Turns a value into a whole number.

TO_INTEGER(' 15.15 ')
=> 15

to_json(value)

Turns a value into a JSON string.

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

to_phone_number(value)

Normalizes a phone number into international format.

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

upcase(value)

Makes every letter uppercase.

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

url_decode(value)

Turns percent-encoded sequences (a % followed by two hex digits) back into the characters they stand for.

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

url_encode(value)

Percent-encodes every character that is not a letter or digit, so the value is safe in a URL.

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

usa_zip_code(value)

Keeps only the first five digits, turning a ZIP+4 into a standard U.S. ZIP code.

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