A table of predefined ESQL functions for use with message maps.
Name | ESQL equivalent | Notes |
---|---|---|
Numeric functions: abs absval acos asin atan atan2 bitand bitnot bitor bitxor ceil ceiling cos cosh cot degrees exp floor in log log10 mod power radians rand sign sin sinh sqrt tan tanh truncate | ESQL function of the name same name such as ABS and ABSVAL. | The same parameters apply as for ESQL. |
String functions: left length lower lcase ltrim replace replicate right rtrim space translate upper ucase | ESQL function of the same name such as LEFT and LENGTH. | The same parameters apply as for ESQL. |
Field functions: bitstream fieldname fieldnamespace fieldtype fieldvalue lastmove samefield | ESQL function of the same name such as BITSTREAM and FIELDNAME. | The same parameters apply as for ESQL. |
asbitstream | ASBITSTREAM (fieldRef) | Optional parameters are not supported. |
cardinality | CARDINALITY | The same parameters apply as for ESQL. |
coalesce | COALESCE | The same parameters apply as for ESQL. |
current-date | CURRENT_DATE | No parameters apply. |
current-gmtdate | CURRENT_GMTDATE | No parameters apply. |
current-gmttime | CURRENT_GMTTIME | No parameters apply. |
current-gmttimestamp | CURRENT_GMTTIMESTAMP | No parameters apply. |
current-time | CURRENT_TIME | No parameters apply. |
current-timestamp | CURRENT-TIMESTAMP | No parameters apply. |
date | DATE | |
for | FOR (expression) | Optional parameters are not supported. |
gmttime | GMTTIME | |
gmttimestamp | GMTTIMESTAMP | |
interval-year | INTERVAL YEAR | The same parameters apply as for ESQL.
Some examples: esql:interval-minute('90') |
interval-year-to-month | INTERVAL YEAR TO MONTH | |
interval-month | INTERVAL MONTH | |
interval-day | INTERVAL DAY | |
interval-day-to-hour | INTERVAL DAY TO HOUR | |
interval-day-to-minute | INTERVAL DAY TO MINUTE | |
interval-day-to-second | INTERVAL DAY TO SECOND | |
interval-hour | INTERVAL HOUR | |
interval-hour-to-minute | INTERVAL HOUR TO MINUTE | |
interval-hour-to-second | INTERVAL HOUR TO SECOND | |
interval-minute | INTERVAL MINUTE | |
interval-minute-to-second | INTERVAL MINUTE TO SECOND | |
interval-second | INTERVAL SECOND | |
is-null | Operand IS NULL | Some examples: esql:is-null($source/po:purchaseOrder/po:comment) |
like | source LIKE pattern | For example: esql:like($source/po:purchaseOrder/shipTo/first_name,'Fred') |
source LIKE pattern ESCAPE EscapeChar | For example: esql:like($source/po:purchaseOrder/shipTo/zip,'L6F$_1C7','$') |
|
local-timezone | LOCAL_TIMEZONE | |
nullif | NULLIF | The same parameters apply as for ESQL. |
overlay | OVERLAY Str1 PLACING Str2 FROM Start | For example: esql:overlay($source/po:purchaseOrder/shipTo/city,'abc',2) |
OVERLAY Str1 PLACING Str2 FROM Start For Length | For example: esql:overlay($source/po:purchaseOrder/shipTo/city,'abcde',2,3) |
|
position | POSITION searchExp IN SourceExp | For example: esql:position('aet',$source/po:purchaseOrder/shipTo/first_name) |
POSITION searchExp IN SourceExp FROM FromExp | For example: esql:position('do',$source/po:purchaseOrder/shipTo/last_name,1) |
|
POSITION searchExp IN SourceExp FROM FromExp REPEAT RepeatExp | For example: esql:position('a',$source/po:purchaseOrder/billTo/first_name,1,2) |
|
round | ROUND | Optional parameters are not supported. |
sqlcode | SQLCODE | No parameters apply. |
sqlerrortext | SQLERRORTEXT | |
sqlnativeerror | SQLNATIVEERROR | |
sqlstate | SQLSTATE | |
time | TIME | |
timestamp | TIMESTAMP | The same parameters apply as for ESQL. For example: esql:gmttimestamp('1999-12-31 23:59:59.999999') |
trim-leading | TRIM LEADING FROM Source | For example: esql:trim-leading($source/po:purchaseOrder/shipTo/state) |
TRIM LEADING Singleton FROM Source | For example: esql:trim-leading('G',$source/po:purchaseOrder/shipTo/zip) |
|
trim-trailing | TRIM TRAILING FROM Source | For example: esql:trim-trailing($source/po:purchaseOrder/billTo/last_name) |
TRIM TRAILING Singleton FROM Source | For example: esql:trim-trailing('e',$source/po:purchaseOrder/billTo/street) |
|
trim-both | TRIM BOTH FROM Source | For example: esql:trim-both($source/po:purchaseOrder/shipTo/city) |
TRIM BOTH Singleton FROM Source | For example: esql:trim-both(",$source/po:purchaseOrder/shipTo/city) |
|
trim | TRIM Source | For example: esql:trim($source/po:purchaseOrder/shipTo/city) |
TRIM Singleton FROM Source | For example: esql:trim(",$source/po:purchaseOrder/shipTo/city) |
|
uuidasblob | UUIDASBLOB | Takes zero or more parameters as in ESQL. |
uuidaschar | UUIDASCHAR |