5. AMPS Functions

In the AMPS expression language, a function can be used in any place an identifier or literal value can be used.

All AMPS functions return a value. During evaluation of an AMPS expression, AMPS calls the functions in the expression and uses the results to evaluate the expression.

The results of a call to an AMPS function can be used as the parameter to an AMPS function. For example, the following is a valid expression:

REVERSE(SUBSTR('fandango',5)) == 'ogna'

In this case, AMPS first evaluates the SUBSTR function, which requests the subset of the string fandango, starting at position 5. That function returns ango. AMPS then uses the string ango as the input to the REVERSE function, which returns the result ogna.

This section describes the functions provided with AMPS.

The following table lists the available functions by category:

Category Function Types Provided
String String Comparison Functions
Converting Arrays to Strings
Concatenating Strings
Managing String Case
Replacing Text in Strings
String Manipulation Functions
Date and Time

Date and Time Manipulation

see also Legacy Messaging Compatibility Functions

Geospatial Geospatial Functions
Numeric Numeric Functions
Checksum CRC Function
Message Current Message Functions
Client Client Information Functions
Working with NULL values Finding Non-NULL Values

Table 5.1: AMPS function categories

All AMPS functions return a value. In the AMPS expression language, a function can be used in any place an identifier or literal value can be used.

String Comparison Functions

AMPS includes several types of string comparison operators.

  • Case-sensitive exact matches. The IN, =, BEGINS WITH, ENDS WITH, and INSTR operators do literal matching on the contents of a string. These operators are case-sensitive.
  • Case-insensitive exact matches. AMPS also provides two case-insensitive operators: INSTR_I, a case-insensitive version of INSTR, and a case-insensitive equality operator, STREQUAL_I.
  • Regular expression matches. AMPS also provides full regular expression matching using the LIKE operator, described in regex matching and Chapter 5.

The = operator tests whether a field exactly matches the literal string provided.

/status = 'available'

/orderId = 'F327AC'

BEGINS WITH and ENDS WITH test whether a field begins or ends with the literal string provided. The operators return TRUE or FALSE:

/Department BEGINS WITH ('Engineering')

/path NOT BEGINS WITH ('/public/dropbox')

/filename ENDS WITH ('txt')

/price NOT ENDS WITH ('99')

AMPS allows you to use set comparisons with BEGINS WITH and ENDS WITH. In this case, the filter matches if the string in the field BEGINS WITH or ENDS WITH any of the strings in the set:

/Department BEGINS WITH ('Engineering', 'Research', 'Technical')

/filename ENDS WITH ('gif', 'png', 'jpg')

The INSTR operator allows you to check to see if one string occurs within another string. For this operator, you provide two string values. If the second string occurs within the first string, INSTR returns the position at which the second string starts, or 0 if the second string does not occur within the first string. Notice that the first character of the string is 1 (not 0). For example, the expression below tests whether the string critical occurs within the /eventLevels field.

INSTR(/eventLevels, "critical") != 0

AMPS also provides INSTR_I and STREQUAL_I functions for performing case-insensitive comparisons:

STREQUAL_I(/couponCode, 'QED') == 1

INSTR_I(/symbolList, 'MSFT') != 0
Function or Operator Parameters Description
=

The string to be compared

The string to compare

Case-sensitive

Returns true if the string to be compared is identical to the string to compare.

/state = 'Ohio'
BEGINS WITH

The string to be compared

A list of strings to compare

Case-sensitive

Returns true if the string to be compared begins with any of the strings in the list.

/state
  BEGINS WITH ('North', 'South')
ENDS WITH

The string to be compared

A list of strings to compare

Case-sensitive

Returns true if the string to be compared ends with any of the strings in the list.

/state
  ENDS WITH ('Dakota', 'Carolina')
INSTR

The string to be compared

The string to compare

Case-sensitive

Returns the position at which the second string starts, or 0 if the second string does not occur within the first string.

INSTR(/state, 'i') != 0
INSTR_I

The string to be compared

The string to compare

Case-insensitive

Returns the position at which the second string starts, or 0 if the second string does not occur within the first string. This function is not unicode-aware.

INSTR_I(/state, 'i') != 0
STREQUAL_I

The string to be compared

The string to compare

Case-insensitive

Returns true if, when both strings are transformed to the same case, the string to be compared is identical to the string to compare. This function is not unicode-aware.

STREQUAL_I(/state, 'oHIO') != 0
LENGTH The string to be counted

Returns the length of the provided string

LENGTH(/streetAddress) > 50

Table 5.2: AMPS string comparison

Converting Arrays to Strings

AMPS contains a function, ARRAY_TO_STRING, that can be used to convert an array to a single string.

Function Parameters Description
ARRAY_TO_STRING array, delimiter, null_replacement Returns a string comprised of the elements of the array, separated by the provided delimiter. NULL values in the array are replaced with the provided null_replacement value.

Table 5.3: AMPS array to string function

Concatenating Strings

AMPS provides a function, CONCAT that can be used for constructing strings. The CONCAT function takes any number of parameters and returns a string constructed from those parameters. The function can accept both XPath identifiers and literal values.

The CONCAT function can be used in any AMPS expression that uses a string. For example, you could CONCAT in a filter as follows:

CONCAT(/firstName, " ", /lastName) = 'George Orwell'

CONCAT can be combined with other expressions, including conditional expressions. A mailingAddressName field in a view could be constructed as follows:

<Field>CONCAT(/firstName, " ", /lastName,
    IF(/suffix NOT NULL, CONCAT(", ", /suffix), "") )
    AS /mailingAddressName</Field>

Managing String Case

AMPS provides the UPPER and LOWER functions to produce a string in a specific case. This can be useful when constructing fields, or when an expression needs case-insensitive comparisons against a group of values using the IN clause.

As described above in String Comparison Functions, AMPS provides INSTR_I and STREQUAL_I functions for performing case-insensitive comparisons. In some cases, particularly when using strings with the IN clause, it is more efficient to simply convert the string to a known case.

The UPPER and LOWER functions are not unicode-aware; these functions will not produce the correct data when used with multibyte characters. For example, you might compare an incoming field of unknown case to a set of known values as follows:

UPPER(/ticker) IN ('MSFT', 'IBM', 'RHAT', 'DIS')
Function Parameters Description
UPPER The string to transform Returns the input string, transformed to upper case. This function is not unicode aware.
LOWER The string to transform Returns the input string, transformed to lower case. This function is not unicode aware.

Table 5.4: AMPS string manipulation functions

Replacing Text in Strings

AMPS provides a pair of functions, REPLACE and REGEXP_REPLACE, that replace text within strings. The REPLACE function does a literal match of the string to be replaced, while REGEXP_REPLACE uses a PCRE pattern to find the string to be replaced.

The following expressions all evaluate as true:

REPLACE('fandango', 'dan', 'din') == 'fandingo'

REGEXP_REPLACE('fandango','n.*n', 'r') == 'fargo'
Function Parameters Description
REPLACE string to transform, string to match, replacement text Returns the input string, with all occurrences of the string to match replaced with the replacement text
REGEXP_REPLACE string to transform, pattern to match, replacement text Returns the input string, with all occurrences of the pattern to match replaced with the replacement text

Table 5.5: AMPS string replacement functions

String Manipulation Functions

AMPS provides a function, SUBSTR, that can be used for returning a subset of a string. There are two forms of this function.

The first form takes the source string and the position at which to begin the substring. You can use a negative number to count backward from the end of the string. AMPS starts at the position specified, and returns a string that starts at the specified position and goes to the end of the string. If the provided position is before the beginning of the string, AMPS starts at the beginning of the string. If the provided position is past the end of the string, AMPS returns a zero-length string, which evaluates to NULL. If the provided position is before the beginning of the string, AMPS returns the full string.

For example, the following expressions are all TRUE:

SUBSTR("fandango", 4) == "dango"

SUBSTR("fandango", 1) == "fandango"

SUBSTR("fandango", -2) == "go"

SUBSTR("fandango", -99) == "fandango"

SUBSTR("fandango", 99) IS NULL

The second form of SUBSTR takes the source string, the position at which to begin the substring, and the length of the substring. Notice that SUBSTR considers the first character in the string to be position 1 (rather than position 0), as demonstrated below. AMPS will not return a string larger than the source string. As with the two-argument form, if the starting position is before the beginning of the string, AMPS starts at the beginning of the string. If the starting position is after the end of the source string, AMPS returns an empty string which evaluates to NULL.

For example, the following expressions are all true:

SUBSTR("fandango", 1, 3) == "fan"

SUBSTR("fandango", -4, 2) == "an"

SUBSTR("fandango", -8, 8) == "fandango"

SUBSTR("fandango", -23, 3) == "fan"

SUBSTR("fandango", 99, 8) IS NULL

AMPS also provides simplified forms of SUBSTR, which simply take the leftmost or rightmost characters from a string. For example, the following expressions all evaluate as true:

LEFT("fandango", 3)  == "fan"

RIGHT("fandango", 2) == "go"

AMPS provides a set of functions that work with whitespace or other delimiter characters. For example, the following expressions are all true:

TRIM(" Lancelot ")  == "Lancelot"

LTRIM(" Lancelot ") == "Lancelot "

RTRIM(" Lancelot ") == " Lancelot"

These functions accept an optional second parameter that specifies the delimiters to remove:

TRIM("=@=Lancelot=@=", "=@") == "Lancelot"

The REVERSE function simply reverses the input string:

REVERSE("fandango") == "ognadnaf"
Function Parameters Description
SUBSTR string to process, starting position, [length] Returns a portion of the input string, starting at the starting position and ending after the specified length. If the length is not provided, returns the portion of the string from the starting position to the end of the string.
TRIM string to transform, [characters to trim]

Returns the input string, with all leading and trailing characters in the set of characters to trim removed.

The characters to trim parameter is optional. When not provided, the parameter defaults to " " (that is, a space character).

LTRIM string to transform, [characters to trim]

Returns the input string, with all leading characters in the set of characters to trim removed.

The characters to trim parameter is optional. When not provided, the parameter defaults to " " (that is, a space character).

RTRIM string to process, [characters to trim]

Returns the input string, with all trailing characters in the set of characters to trim removed.

The characters to trim parameter is optional. When not provided, the parameter defaults to " " (that is, a space character).

LEFT string to process, number of characters Returns the leftmost number of characters from the provided string.
RIGHT string to process, number of characters Returns the rightmost number of characters from the provided string.
REVERSE string to process, Returns the provided string in reverse.

Table 5.6: AMPS string manipulation functions

Date and Time Functions

AMPS includes functions for working with date and time values. This section covers functions loaded into AMPS by default. AMPS also includes functions for working with date and time in the Legacy Messaging Compatibility layer.

Function Parameters Description
STRFTIME format string, timestamp

Produces a string that contains a representation of the provided timestamp, formatted as specified in the provided format string. The format string uses the same format specifiers as the standard strftime(3) function.

This function also supports the additional format specifier %f to format microseconds, and the format specifier %03f to format milliseconds.

The length of the string produced for the time is limited to 128 bytes.

STRPTIME time string, format string

This function interprets the time string provided as a timestamp, with the format string specifying how to interpret the time string.

This function returns a double.

The format string uses the same format specifiers as the standard strptime(3) function.

This function also supports the additional format specifier %f to parse microseconds, and the format specifier %03f to parse milliseconds.

UNIX_TIMESTAMP none

Returns the current timestamp as a double.

Notice that a UNIX timestamp is seconds elapsed since 00:00 on January 1, 1970 in UTC and is independent of the timezone of the local system.

Table 5.7: AMPS Date and Time functions

Geospatial Functions

AMPS includes a function for calculating the distance from a signed latitude and longitude.

Function Parameters Description
GEO_DISTANCE first_latitude, first_longitude, second_latitude, second_longitude

Returns a double that contains the distance between the point identified by first_latitude, first_longitude and second_latitude, second_longitude in meters.

For example, given a home point and a message containing /lat and /long fields, you could use the following expression to calculate the distance from home.

GEO_DISTANCE(
     /lat,
     /long,
     40.786337,
     -119.206508)

AMPS uses the haversine formula when computing distances.

Table 5.8: AMPS Geospatial Functions

Numeric Functions

AMPS includes the following functions for working with numbers.

Function Parameters Description
ABS number

Returns the absolute value of a number.

For example, the following filter will be TRUE when the difference between /a and /b is greater than 5, regardless of whether /a or /b is larger.

ABS(/a - /b) > 5
GREATEST list of numbers to compare

Returns the largest of the provided numbers, or NaN if no argument is a number.

GREATEST(/requestedQty, /availableQty, /reserveQty)
LEAST list of numbers to compare

Returns the smallest of the provided numbers, or NaN if no argument is a number.

LEAST(/requestedQty, /availableQty, /reserveQty)
CEILING number to round

Returns the value rounded upward to the next greatest integer. Returns an integer unchanged.

CEILING(/estimatedDelta)
FLOOR number to round

Returns the value rounded downward to the next lower integer. Returns an integer unchanged.

FLOOR(/estimatedReturn)
EXP exponent to use

Returns e raised to the power of the provided exponent.

EXP(3.14)
LN number

Returns the natural logarithm of the provided number.

LN(/time)
LOG2 number

Returns the base-2 logarithm of the provided number.

LOG2(/recordSize)
LOG10 number

Returns the base-10 logarithm of the provided number.

LOG10(/displayData)
POWER base, exponent

Returns the value of base raised to the power of exponent.

POWER(/number, 3)
SQRT number

Returns the square root of the provided number.

SQRT(2304)
COS number

Returns the cosine of the provided number.

COS(/x)
ACOS number

Returns the arcosine of the provided number.

ACOS(/x)
SIN number

Returns the sine of the provided number.

SIN(/x)
SINH number

Returns the hyperbolic sine of the provided number.

SINH(/x)
ASIN number

Returns the arcsine of the provided number.

ASIN(/x)
COSH number

Returns the hyperbolic cosine of the provided number.

COSH(/x)
COT number

Returns the cotangent of the provided number.

COT(/x)
ATAN number

Returns the arctangent of the provided number.

ATAN(/x)
ATAN2 number, number

Returns the arctangent of the provided numbers.

ATAN2(/y,/x)
TAN number

Returns the tangent of the provided number.

TAN(/x)
TANH number

Returns the hyperbolic tangent of the provided number.

TANH(/x)
MD5 string

Returns the MD5 checksum of the provided string.

MD5(/data)
RADIANS number

Returns the provided number converted from degrees to radians.

RADIANS(/deg)
DEGREES number

Returns the provided number converted from radians to degrees.

DEGREES(/rad)
SIGN number

Returns the sign of the provided number. If the number is less than 0, returns -1 is greater than 0, returns 1. Otherwise, the number is 0 and the function returns 0.

SIGN(/result)
ROUND number, [number of decimal places]

Returns a number rounded to the specified number of decimal places.

The number of decimal places is optional. When not provided, the number defaults to 0.

The number of decimal places can be positive or negative. When the number is positive, the number specifies the number of digits to the right of the decimal place to round at. When the number is negative, the number specifies the number of digits to the left of the decimal place to round at.

For example, you could use the following expression in a view to limit the precision of the /price field of the source topic to 2 decimal places.

ROUND(/price, 2) AS
/price
WIDTH_BUCKET expression, min, max, bucket count

The bucket count argument specifies the number of buckets to create over the range defined by min and max. min is inclusive, while max is not. The value from expression is assigned to a bucket, and the function returns a corresponding bucket number. When expression falls outside the range of buckets, the function returns either 0 or max + 1, depending on whether expression is lower than min or greater than or equal to max.

WIDTH_BUCKET(/percentage, 0, 100, 10) AS
/displayBar

Table 5.9: AMPS Numeric Functions

CRC Functions

AMPS includes functions that computes a CRC checksum over a string. This function is useful for creating a numeric identifier from a string representation. This is commonly-used to create a shortened representation of the string, or to provide input for a MOD calculation.

Function Parameters Description
CRC32 string

Returns an integer calculated as a checksum of the provided string.

This function returns a 32-bit integer.

CRC64 string

Returns an integer calculated as a checksum of the provided string.

This function returns a 64-bit integer.

Table 5.10: AMPS CRC functions

Message Functions

AMPS includes functions that can be used to refer to the current message being processed.

Function Parameters Description Available for
MESSAGE_SIZE (none) Returns the size of the payload of the current message, in bytes. (all messages)
CORRELATION_ID (none)

Returns the correlation ID of the of the current message as a string.

Returns NULL if there is no correlation ID for the current message.

(all messages)
LAST_UPDATED (none)

Returns a timestamp for the last time that a message in the SOW was updated, as a double.

For a subscription (including the subscription part of a sow_and_subscribe command), the LAST_UPDATED value will be the current timestamp. This function is most useful for queries of a topic in the SOW.

Notice that this field is set based on when the local instance has updated the message. For replicated topics, this means that a given message will have different values on different instances.

queries of a SOW topic
BOOKMARK (none)

Returns the bookmark for the current message, if one is available. Notice that messages retrieved from a SOW topic using a query return NULL for BOOKMARK, since the SOW does not store the bookmark of a message.

Bookmarks are assigned using a combination of an identifier derived from the client name and a sequence number. When working with bookmarks, 60East recommends treating bookmarks as opaque identifiers. In particular, bookmarks are not guaranteed to sort in any particular order between different publishers.

AMPS only assigns bookmarks when a message is stored in the transaction log. Messages that are not in the transaction log do not have bookmarks assigned.

subscriptions to a transaction-logged topic, bookmark subscriptions, subscriptions to a message queue, replication filters
LAST_READ (none)

Returns a timestamp for the last time that this message was read from the SOW, as a double.

This function only returns a value for messages in a topic in the SOW. The LAST_READ time for a message resets when AMPS restarts.

Notice that this field is set based on when the local instance processes a read of the message. For replicated topics, this means that a given message will have different values on different instances.

queries of a SOW topic
TOPIC_NAME (none) Returns the topic name for the message currently being processed. (all messages)
LAST_LEASED (none)

For a message in a queue, returns a timestamp for the last time this message was leased from this instance, as a double.

Returns NULL for a message that is not in a queue.

Notice that this timestamp is set based on when the local instance leased the message. This counter is reset when the instance restarts.

queries of a message queue, subscription to a message queue, sow delete by filter for a message queue
LEASE_COUNT (none)

For a message in a queue, returns the number of times the message has been leased from this instance as a double.

Returns NULL for a message that is not in a queue.

Notice that this counter is set based on leases from the local instance. This counter is reset when the instance restarts, and does not track leases from other instances.

queries of a message queue, subscription to a message queue, sow delete by filter for a message queue

Table 5.11: AMPS message functions

When used in view construction or aggregate definition, these functions refer to the incoming message that is prompting the update to the view or aggregate, not to the constructed message that is the result of the update. For example, a Field like this in a view projection:

<Projection>
  ...
  <Field>TOPIC_NAME() AS /theTopic</Field>
  ...
</Projection>

will return the topic name of the topic that prompted the update to the view, not the name of the view itself.

Client Functions

AMPS includes functions that return information about the currently-connected client. As with the message functions, these functions return information about the client that prompted the operation, if one is present.

Function Parameters Description Available for
CLIENT_NAME (none) Returns the name of the currently connected client. subscriptions to a topic or conflated topic, enrichment and preprocessing
USER (none) Returns the user ID of the currently connected client. subscriptions to a topic or conflated topic, enrichment and preprocessing
REMOTE_ADDRESS (none) Returns the remote address of the currently connected client. subscriptions to a topic or conflated topic, enrichment and preprocessing
CLIENT_VERSION (none) Returns the version string reported by the currently connected client. subscriptions to a topic or conflated topic, enrichment and preprocessing

Table 5.12: AMPS client functions

Coalesce Function

AMPS includes a function that accepts any number of arguments and returns the first argument that is not NULL.

Function Parameters Description
COALESCE value (any number of values may be provided) Returns the first value that is not NULL.

Table 5.13: AMPS coalesce function