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 | |
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
, andINSTR
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 ofINSTR
, 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 |
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 |
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 |
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 This function also
supports the additional
format specifier The length of the string produced for the time is limited to 128 bytes. |
STRPTIME |
time string, format string | This function interprets
the This function returns a double. The format string uses
the same format
specifiers as the
standard This function also
supports the additional
format specifier |
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 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 ABS(/a - /b) > 5
|
GREATEST |
list of numbers to compare | Returns the largest of the provided numbers,
or GREATEST(/requestedQty, /availableQty, /reserveQty)
|
LEAST |
list of numbers to compare | Returns the smallest of the provided numbers,
or 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 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 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 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 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 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 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 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