# Functions

A collection of built-in SQL functions of Lenses. You can also enrich with additional user defined functions.

Function | Description | Streaming | Snapshot |
---|---|---|---|

AS_NON_NULLABLE (expr) | Returns the provided value with its type changed from the original type to its non nullable version | ✓ | ✓ |

AS_NULLABLE (expr) | Returns the provided value with its type changed from the original type to its nullable version | ✓ | ✓ |

CAST (dt AS int) | Enables conversion of values from one data type to another | ✓ | ✓ |

COALESCE (value, prevValue) | Returns the first non-null expression in the expression list | ✓ | ✓ |

DUMP (expr) | Show the internal representation of a value | ✓ | ✓ |

EXISTS (field) | Returns true if the given field is present false otherwise | ✓ | ✓ |

ISNOTNULL (expr) | Returns true if the input is not null; false otherwise | ✓ | ✓ |

ISNULL (expr) | Returns true if the input is null; false otherwise | ✓ | ✓ |

SIZEOF (expr) | Returns the number of elements in an array | ✓ | ✓ |

TYPEOF () | Returns the object type of a complex expression. This can only be used when the format on the wire includes the details of the objects full name | no | ✓ |

## String functions

Function | Description | Streaming | Snapshot |
---|---|---|---|

ABBREVIATE (expr, lengthExpr) | Abbreviates the expression to the given length and appends ellipses | ✓ | ✓ |

BASE64 (expr) | Returns the input string using base64 algorithm | ✓ | ✓ |

CAPITALIZE (expr) | Capitalizes the first letter of the expression | ✓ | ✓ |

CENTER (target,size,padExpr) | Centers a String in a larger String of size N | ✓ | ✓ |

CHOP (expr) | Returns the last character from an expression of type string | ✓ | ✓ |

CONCATENATE (expr1, expr2, expr3) | Returns the string representation of concatenating each `expression` in the list. Null fields are left out | ✓ | ✓ |

CONCAT (expr1, expr2, expr3) | Alias for CONCATENATE | ✓ | ✓ |

CONTAINS (sourceExpr,targetExpr) | Returns true if an expression contains the given substring | ✓ | ✓ |

DECODE64 (expr) | Decodes a Base64 encrypted string | ✓ | ✓ |

DELETEWHITESPACE (expr) | Removes all whitespace from an expression of type string | ✓ | ✓ |

DIGITS (expr) | Retains only the digits from a string expression | ✓ | ✓ |

DROPLEFT (expr, lengthExpr) | Removes the left most ‘length’ characters from a string expression | ✓ | ✓ |

DROPRIGHT (expr, lengthExpr) | Removes the left most ‘length’ characters from a string expression | ✓ | ✓ |

ENDSWITH (sourceExpr, targetExpr) | Returns true if an expression ends with the given substring | ✓ | ✓ |

INDEXOF (expr, substringExpr) | Returns the index of a substring in an expression | ✓ | ✓ |

LENGTH expr | Returns the length of a string. Calculates length using characters as defined by UTF-16 | ✓ | ✓ |

LEN expr | Alias for LENGTH | ✓ | ✓ |

LOWERCASE (strExpr) | Returns the expression in lowercase | ✓ | ✓ |

LOWER (strExpr) | Alias for LOWERCASE | ✓ | ✓ |

LEFTPAD (strExpr, lengthExpr, padExpr) | Prepends the value of padExpr to the value of strExpr until the total length is lengthExpr | ✓ | ✓ |

LPAD (strExpr, lengthExpr, padExpr) | Alias for LEFTPAD | ✓ | ✓ |

REGEXP (strExpr, regexExpr) | Returns the matched groups otherwise null | ✓ | ✓ |

REGEX (strExpr, regexExpr) | Alias for REGEXP | ✓ | ✓ |

REPLACE (sourceExpr, targetExpr, replaceExpr) | Returns a new string in which all occurrences of a specified String in the current string are replaced with another specified String | ✓ | ✓ |

REVERSE (expr) | Reverses the order of the elements in the input | ✓ | ✓ |

RIGHTPAD (strExpr, lengthExpr, padExpr) | Appends the value of padExpr to the value of strExpr until the total length is lengthExpr | ✓ | ✓ |

RPAD (strExpr, lengthExpr, padExpr) | Alias for RIGHTPAD | ✓ | ✓ |

STARTSWITH (exprSource, exprTarget) | Returns true if an expression starts with the given substring | ✓ | ✓ |

STRIPACCENTS (expr) | Removes diacritics (approximately the same as accents) from an expression. The case will not be altered | ✓ | ✓ |

SUBSTRING (expr, startIndexExpr, endIndexExpr) | Returns a new string that is a substring of this string | ✓ | ✓ |

SUBSTR (expr, startIndexExpr) | Alias for SUBSTRING | ✓ | ✓ |

SWAPCASE (expr) | Swaps the case of a string expression | ✓ | ✓ |

TAKELEFT (expr, lengthExpr) | Returns the left most ‘length’ characters from a string expression | ✓ | ✓ |

TAKERIGHT (expr, lengthExpr) | Returns the right most ‘length’ characters from a string expression | ✓ | ✓ |

TRIM (expr) | Removes leading and trailing spaces from the input expression | ✓ | ✓ |

TRUNCATE (strExpr, nExpr) | Truncates a string so that it has at most N characters | ✓ | ✓ |

UNCAPITALIZE (expr) | Changes the first letter of each word in the expression to lowercase | ✓ | ✓ |

UPPER (strExpr) UPPERCASE (strExpr) | Returns the expression in uppercase | ✓ | ✓ |

UUID () | Returns an universally unique identifier | ✓ | ✓ |

## Obfuscation functions

They allow to redact the value to avoid providing the full content details. Useful when querying data containing customers personal identifiable information like credit card.

Name | Description | Streaming | Snapshot |
---|---|---|---|

ANONYMIZE (strExpr) | Obfuscates the entire string input | ✓ | ✓ |

MASK (strExpr) | Alias for ANONYMIZE | ✓ | ✓ |

EMAIL (emailExpr) | Anonymize the value and obfuscates an email address | ✓ | ✓ |

FIRST1 (strExpr) | Anonymize the value and only keeps the first character | ✓ | ✓ |

FIRST2 (strExpr) | Anonymize the value and only keeps the first two characters | ✓ | ✓ |

FIRST3 (strExpr) | Anonymize the value and only keeps the first three characters | ✓ | ✓ |

FIRST4 (strExpr) | Anonymize the value and only keeps the first four characters | ✓ | ✓ |

LAST1 (strExpr) | Anonymize the value and only keeps the last character | ✓ | ✓ |

LAST2 (strExpr) | Anonymize the value and only keeps the last two characters | ✓ | ✓ |

LAST3 (strExpr) | Anonymize the value and only keeps the last three characters | ✓ | ✓ |

LAST4 (strExpr) | Anonymize the value and only keeps the last four characters | ✓ | ✓ |

INITIALS (strExpr) | Anonymize the value and only keeps the initials of all the words in the input | ✓ | ✓ |

## Numeric functions

Name | Description | Streaming | Snapshot |
---|---|---|---|

`%` | The remainder operator (%) computes the remainder after dividing its first operand by its second i.e. `numExpr % numExpr` | ✓ | ✓ |

`/` | Divides one number by another (an arithmetic operator) i.e. `numExpr / numExpr` | ✓ | ✓ |

`-` | Subtracts one number from another (an arithmetic operator) i.e. `numExpr - numExpr` | ✓ | ✓ |

`*` | Multiplies one number with another (an arithmetic operator) i.e. `numExpr * numExpr` | ✓ | ✓ |

`+` | Adds one number to another (an arithmetic operator) i.e. `numExpr + numExpr` | ✓ | ✓ |

`-` (negative) | Returns the negative of the value of a numeric expression (a unary operator) i.e. `-numExpr` | ✓ | ✓ |

ABS (numExpr) | Returns the absolute value of an `expression` that evaluates to a number type | ✓ | ✓ |

ACOS (numExpr) | Returns the trigonometric arc cosine of an expression | ✓ | ✓ |

ASIN (numExpr) | Returns the trigonometric arc sine of an expression | ✓ | ✓ |

ATAN (numExpr) | Returns the trigonometric arc tangent of an expression | ✓ | ✓ |

CBRT (numExpr) | Returns the cube root of numExpr | ✓ | ✓ |

CEIL (numExpr) | Returns the absolute value of an `expression` | ✓ | ✓ |

COSH (numExpr) | Returns the hyperbolic cosine of an expression | ✓ | ✓ |

COS (numExpr) | Returns the trigonometric cosine of an expression | ✓ | ✓ |

DEGREES (numExpr) | Converts the input expression to degrees | ✓ | ✓ |

DISTANCE (x1,y1,x2,y2) | Calculates the distance between two points using the haversine method | ✓ | ✓ |

FLOOR (numExpr) | Returns the largest value not greater than the argument | ✓ | ✓ |

MAX (numExpr1,numExpr2,numExpr3) | Returns the maximum element from an arbitrary number of given elements | ✓ | ✓ |

MIN (numExpr1,numExpr2,numExpr3) | Returns the minimum element from an arbitrary number of given elements | ✓ | ✓ |

MOD (numExpr, numExpr) | Alias for `%` | ✓ | ✓ |

NEG (numExpr) | Returns the negative value of an `expression` it has to evaluate to a number type | ✓ | ✓ |

POW (numExpr1, numExpr2) | Returns numExp1 raised to the numExp2 power | ✓ | ✓ |

RADIANS (numExpr) | Converts the input expression to radians | ✓ | ✓ |

RANDINT () | Returns a random integer | ✓ | ✓ |

ROUND (numExpr) | Returns the closest integer of an `expression` , with ties rounding towards positive infinity | ✓ | ✓ |

SIGN (numExpr) | Returns +1 if a value is positive or -1 if a value is negative | ✓ | ✓ |

SINH (numExpr) | Returns the hyperbolic sine of an expression | ✓ | ✓ |

SIN (numExpr) | Returns the trigonometric sine of an expression | ✓ | ✓ |

SQRT (numExpr) | Returns the square root of numExpr | ✓ | ✓ |

TANH (numExpr) | Returns the hyperbolic tangent of an expression | ✓ | ✓ |

TAN (numExpr) | Returns the trigonometric tangent of an expression | ✓ | ✓ |

## Date and time functions

Name | Description | Streaming | Snapshot |
---|---|---|---|

DATE () | Provides the current ISO date value | ✓ | ✓ |

CONVERT_DATETIME (strExpr, fromPattern, toPattern) | Converts the string format of a date [and time] to another using the pattern provided | ✓ | ✓ |

CONVERTDATETIME (strExpr, fromPattern, toPattern) | Alias for CONVERT_DATETIME | ✓ | ✓ |

DATETIME () | Provides the current ISO date and time | ✓ | ✓ |

DATE_TO_STR (strExpr, pattern) | Converts a date time value to a string using the pattern provided | ✓ | ✓ |

DAY (expr) | Extracts the day component of an expression that is of type timestamp | ✓ | ✓ |

HOUR <small)(expr) | Extracts the hour component of an expression that is of type timestamp | ✓ | ✓ |

MINUTE <small)(dataExpr) | Extracts the minute component of an expression that is of type timestamp | ✓ | ✓ |

MONTH_TEXT <small)(dataExpr) | Returns the month name | ✓ | ✓ |

MONTH <small)(dataExpr) | Extracts the month component of an expression that is of type timestamp | ✓ | ✓ |

SECOND <small)(dataExpr) | Extracts the second component of an expression that is of type timestamp | ✓ | ✓ |

TOMORROW () | Returns the current date time plus 1 day | ✓ | ✓ |

TO_DATETIME (strExpr, pattern) | Converts a string representation of a datetime into epoch value using the pattern provided | ✓ | ✓ |

TO_DATE (strExpr, pattern) | Converts a string representation of a date into epoch value using the pattern provided | ✓ | ✓ |

TO_TIMESTAMP (longExpr) | Converts a long (epoch) to a date and time type | ✓ | ✓ |

TO_TIMESTAMP (strExpr, pattern) | Converts a string using a pattern to a date and time type | ✓ | ✓ |

YEAR (expr) | Extracts the year component of an expression that is of type timestamp | ✓ | ✓ |

YESTERDAY () | Returns the current date time minus 1 day | ✓ | ✓ |

## Aggregated functions

An aggregate function performs a calculation on a set of values, and returns a single value. They cannot be used without a GROUP BY statement.

LSQL engine Snapshot and Streaming modes have different semantics. Thus, some functions which are available for the Streaming and do not apply to Snapshot.

Name | Description | |||
---|---|---|---|---|

AVG (numExpr) | Returns the average of the values in a group. It ignores null value. It can be used with numeric input only | ✓ | ✓ | ✓ |

BOTTOMK (numExpr, N) | Returns the last K lowest ranked values. The ranking is based on how many times a value has been seen | no | no | ✓ |

COLLECT (expr, maxN) | Returns an array in which each value in the input set is assigned to an element of the array | ✓ | ✓ | ✓ |

COLLECT_UNIQUE (expr, maxN) | Returns an array of unique values in which each value in the input set is assigned to an element of the array | ✓ | no | ✓ |

COUNT (*) AS total | Returns the number of records returned by a query or the records in a group as a result of a `GROUP BY` statement | ✓ | ✓ | ✓ |

FIRST (expr) | Returns the first item seen in a group | no | no | ✓ |

LAST (expr) | Returns the last item seen in a group | no | no | ✓ |

MAXK (numExpr, N) | Returns the N largest values of an numExpr | ✓ | no | no |

MAXK_UNIQUE (numExpr, N) | Returns the N largest unique values of an numExpr | ✓ | no | no |

MINK (numExpr, N) | Returns the N smallest values of an numExpr | ✓ | no | no |

MINK_UNIQUE (numExpr, N) | Returns the N smallest unique values of an numExpr | ✓ | no | no |

SUM (numExpr) | Returns the sum of all the values, in the expression. It can be used with numeric input only. Null values are ignored | ✓ | ✓ | ✓ |

TOPK (numExpr, N) | Returns the K highest ranked values. The ranking is based on how many times a value has been seen | no | no | ✓ |

## Record headers functions

Name | Description | Streaming | Snapshot |
---|---|---|---|

HEADERASSTRING (keyStr) | Returns the value of the record header key as a STRING value | no | ✓ |

HEADERASINT (keyStr) | Returns the value of the record header key as an INT value | no | ✓ |

HEADERASLONG (keyStr) | Returns the value of the record header key as a LONG value | no | ✓ |

HEADERASDOUBLE (keyStr) | Returns the value of the record header key as a DOUBLE value | no | ✓ |

HEADERASFLOAT (keyStr) | Returns the value of the record header key as a FLOAT value | no | ✓ |

HEADERKEYS () | Returns all the header keys for the current record | no | ✓ |

## Date math

Lenses offers direct support for manipulating dates, times, and timestamps. This functionality is named Date Math. The expressions can be used in any SQL statement where a general-purpose expression is allowed - for example as part of a where filter, in a group by clause, a select projection, and so on.

Every Date Math expression starts with a base date or time followed by the addition or subtraction of one or more durations. Most of the time, the base would come from a field, but sometimes the base date may need to be modified - for example extracting the minutes component only from a time value. In addition to this, the base can be generated using a function unrelated to any field in the the data record. Check Date and Time functions

Consider a scenario where it is required to compute a month from the value of `subscription_date`

field. A naive implementation might be to add (1000 * 60 * 60 * 24 * 31) milliseconds to the subscription_date. But what is a month - a month can have 28 days, 30 days or 31 days (in the Gregorian calendar). In daylight savings timezones, a month may include an extra hour or might miss an hour. It is hard to handle the uneven nature of date units in a query.

### Syntax

Every Date Math expression starts with a base date or time followed by the addition or subtraction of one or more durations.

The base date or time (from here onward) is derived from a field in a table or a function such as now() or yesterday() that generates datetime values.

Duration operations translate the base date by either adding or subtracting a duration defined using the special Date Math shorthand - this is where the math part of the Date Math name comes from.

The shorthand syntax is a unit value followed by a unit symbol. The symbols are as follows:

- y (year)
- M (month)
- w (week)
- d (day)
- h (hour)
- m (minute)
- s (second)

For example, 60s would constitute 60 seconds, which is equivalent to 1m, and 48h would be equivalent to 2d.

A full Date Math expression would look like the following examples:

```
SELECT [STREAM/TABLE]
now() + 1d as tomorrow
FROM <source>
SELECT [STREAM/TABLE]
customers.age + 1y as next_year
FROM <source>
SELECT [STREAM/TABLE]
orders.delivery.date + 1d - 1h as in23Hours
FROM <source>
//adding a month to a subscription date
SELECT [STREAM/TABLE]
subscription_date + 1M AS renewal
FROM <subscriptions>
//adding a month and a day to a subscription date
SELECT [STREAM/TABLE]
subscription_date + 1M + 1d AS renewal
FROM <subscriptions>
```