# 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`

, 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 |
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 |
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 |
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 |
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 |
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 |
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 The |

`LTRIM` |
string to transform,
[characters to trim] |
Returns the input string, with all leading characters in
the set of The |

`RTRIM` |
string to process,
[characters to trim] |
Returns the input string, with all trailing characters in
the set of The |

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

`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 does not
accept the additional
format specifiers |

`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
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 ```
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 ```
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 ```
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 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 ```
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*