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, [ending position]	Returns a portion of the input string, starting at the starting position and ending at the ending position. If the ending position 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 work 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.

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 does not accept the additional format specifiers %f or %03f.

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(/x,/y)
`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

Table Of Contents

Related Topics

5. AMPS Functions¶

String Comparison Functions¶

Converting Arrays to Strings¶

Concatenating Strings¶

Managing String Case¶

Replacing Text in Strings¶

String Manipulation Functions¶

Date and Time Functions¶

Geospatial Functions¶

Numeric Functions¶

CRC Functions¶

Message Functions¶

Client Functions¶

Coalesce Function¶